Archive for category Data Analysis

BOBJ InfoView Generate File Name with Date

%SI_STARTTIME%

No Comments

Find the first number’s position in a text string with formulas

Select a blank cell where you will return the first number’s position, and enter the formula =MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A2)),FIND({1,2,3,4,5,6,7,8,9,0},A2))) (A2 is the text cell where you will look for the first number’s position) into it, and then drag its Fill Handle to the range as you need. See below screenshots:

No Comments

Calculating Date Difference and excluding weekends and public holiday in Excel

networkingdays.intl

No Comments

Lookup Within a Table using the CALCULATE Function

PowerPivot VLOOKUP equivalent

The RELATED function makes it easy to pull data from one table to another, but what about looking up a value within a table?  I came across this issue recently when a client wanted to compare two rows of data that was reported between consecutive months.  After some search engine queries and some trial and error, I found that VLOOKUP could be accomplished with four DAX functions, CALCULATE, DISTINCT, FILTER, and EARLIER.  The resulting formula looked like this:

=CALCULATE(DISTINCT(‘Table1′[Value]),FILTER(‘Table1’,’Table1′[CalculatedID]=EARLIER(‘Table1′[LookupID])))

The following is a breakdown of that formula into 4 parts:

1)     (‘Table1’[LookupID])

  1. Within Table1 I created another calculated column called LookupID.  LookupID represented another row’s CalculatedID.  In my case the CalculatedID followed the format YYYY-MM-### where the first four characters were the year the data was reported, the second two characters were the numerical month the data was reported, and the last three characters represented a unique identifier for the group the data was reported under (called GroupID).  To generate the LookupID column I subtracted the month and year the data was reported by one month using the DATEADD formula (example below).

FORMAT(DATEADD(‘Table'[MonthYear],-1,MONTH),”yyyy-mm-“))

Then I appended the GroupID field using the CONCATENATE function (example below).

CONCATENATE(FORMAT(DATEADD(‘Table1′[MonthYear],-1,MONTH),”yyyy-mm-“)),’Table1’ [GroupID])

 LookupID now represented the CalculatedID of another row, and this other row contained the previous month’s data.

2)     ‘Table1′[CalculatedID]=EARLIER(‘Table1′[LookupID])

  1. The EARLIER function conducts a table scan of Table1 and finds instances where LookupID is equal to the CalculatedID for the current row.

3)     FILTER(‘Table1’,’Table1′[CalculatedID]=EARLIER(‘Table1′[LookupID])))

  1. In this equation Table1 is filtered by the statement described in part 2.  Therefore only the rows meeting this statement are returned.

4)     CALCULATE(DISTINCT(‘Table1′[Value])…

  1. This part of the equation returns the distinct values for the column “Value” found in the result set returned by the FILTER function described in part 3.

This formula worked well for our client and we were able to compare groups of data between two months and build some nice reports on their SharePoint site.

Excel 2013 and SQL Server 2012

With PowerPivot for Excel 2013 and for SharePoint 2010 environments using SQL Server 2012, you can avoid this lengthy formula by using the new LOOKUPVALUEfunction in DAX.

Author:
John Kerski

No Comments

Splitting An Attribute’s details into columns in MicroStrategy

If they are running the reports in Web, there is button under the Data menu titled Toggle Attribute Forms.  I think that might accomplish what you are after.  Not sure if there is a setting for this in Desktop at the grid level though.

No Comments

Refresh the powerpivot data

Excel 2013 only.

“Refresh the powerpivot data”
$ReportWorkbook = $excel.Workbooks.Open($ReportFilePath)

$ReportWorkbook.Connections | %{
#If PowerPivot Model (Type=7)
#if($_.Type -eq “7”)
#{
” – Refreshing PowerPivot Model: ” + $_.Name
if($_ -ne $null){
$_.Refresh()
}
#}
}
$ReportWorkbook.Save()
$ReportWorkbook.Close()

No Comments

DAX: Count Distinct in Excel

Excel 2010

Count Distinct in Excel 2010, use PowerPivot

1. In the PowerPivot, right click the table name, press “Add Measure”

2. Enter the formula with “CountRows” and “Distinct”, below is an example.

=countrows(distinct(<column>))

However, it count “BLANK” value as 1. In order to prevent that, use below formula

=CALCULATE(countrows(distinct(<column>)),NOT(ISBLANK(<column>)))

 

Excel 2013

=DISTINCTCOUNT(<column>)

However, it count “BLANK” value as 1. In order to prevent that, use below formula

= CALCULATE (

DISTINCTCOUNT(<column>),

NOT(ISBLANK(<column>))

)

No Comments