How to Sort based on Measure Expression in Pivot Table?

[#1]

Hi All,

I have 4 Measures and more than 10 Dimensions in my Comparative Analysis Dashboard.

I have below expression for my Measures:

Measures1 Expression =Sum ({$}{<[$(vYearType)] = [Group 1] :: [$(vYearType)],[$(vQuarterType)] = [Group 1] :: [$(vQuarterType)],[$(vMonthType)] = [Group 1] :: [$(vMonthType)],[$(vDayType)] = [Group 1] :: [$(vDayType)]>}[Employee Count] )

Measures2 Expression=Sum ({$}{<[$(vYearType)] = [Group 1] :: [$(vYearType)],[$(vQuarterType)] = [Group 1] :: [$(vQuarterType)],[$(vMonthType)] = [Group 1] :: [$(vMonthType)],[$(vDayType)] = [Group 1] :: [$(vDayType)]>}[Employee Salary])

Measures3 Expression=Sum ({$}{<[$(vYearType)] = [Group 1] :: [$(vYearType)],[$(vQuarterType)] = [Group 1] :: [$(vQuarterType)],[$(vMonthType)] = [Group 1] :: [$(vMonthType)],[$(vDayType)] = [Group 1] :: [$(vDayType)]>}[Company Profit])

Measures4 Expression=Sum ({$}{<[$(vYearType)] = [Group 1] :: [$(vYearType)],[$(vQuarterType)] = [Group 1] :: [$(vQuarterType)],[$(vMonthType)] = [Group 1] :: [$(vMonthType)],[$(vDayType)] = [Group 1] :: [$(vDayType)]>}[Deposit Amount])

I want to sort my records based on measure selected in Pivot table. How may I achieve this?

[ankitaag]

Hey,

What do you mean by measure selected in pivot table?

[Anonymous]

Pivot table by its nature is always sorted by dimensions - by 1st dimension, and within each 1st dimension value by 2nd dimension, and so on.

The best you can do is to sort first dimension by the expression value.  That is, sort it by expression, and enter expression as is there.

[#4 · Accepted solution]

I created a Variable vSort=

=IF(SubStringCount('|' & Concat(distinct Measure, '|') & '|', '|Employee Count|')=1,     

'Sum ({$}{<[$(vYearType)] = [Group 1] :: [$(vYearType)],[$(vQuarterType)] = [Group 1] :: [$(vQuarterType)],[$(vMonthType)] = [Group 1] :: [$(vMonthType)],[$(vWeekType)] = [Group 1] :: [$(vWeekType)],[$(vDayType)] = [Group 1] :: [$(vDayType)]>} [Employee Count] )'    

,IF(SubStringCount('|' & Concat(distinct Measure, '|') & '|', '|Employee Salary|')=1,     

'Sum ({$}{<[$(vYearType)] = [Group 1] :: [$(vYearType)],[$(vQuarterType)] = [Group 1] :: [$(vQuarterType)],[$(vMonthType)] = [Group 1] :: [$(vMonthType)],[$(vWeekType)] = [Group 1] :: [$(vWeekType)],[$(vDayType)] = [Group 1] :: [$(vDayType)]>}[Employee Salary])'    

,IF(SubStringCount('|' & Concat(distinct Measure, '|') & '|', '|Company Profit|')=1,     

'Sum ({$}{<[$(vYearType)] = [Group 1] :: [$(vYearType)],[$(vQuarterType)] = [Group 1] :: [$(vQuarterType)],[$(vMonthType)] = [Group 1] :: [$(vMonthType)],[$(vWeekType)] = [Group 1] :: [$(vWeekType)],[$(vDayType)] = [Group 1] :: [$(vDayType)]>}[Company Profit])'    

,IF(SubStringCount('|' & Concat(distinct Measure, '|') & '|', '|Deposit Amount|')=1,    

'Sum ({$}{<[$(vYearType)] = [Group 1] :: [$(vYearType)],[$(vQuarterType)] = [Group 1] :: [$(vQuarterType)],[$(vMonthType)] = [Group 1] :: [$(vMonthType)],[$(vWeekType)] = [Group 1] :: [$(vWeekType)],[$(vDayType)] = [Group 1] :: [$(vDayType)]>}[Deposit Amount])'

,1 ))))

and used expression =$(vSort) in Expression under tab Sort of Chart and Table.

It is working perfectly.