topic Re: How to Sort based on Measure Expression in Pivot Table? in QlikView

How to Sort based on Measure Expression in Pivot Table?

Thu, 30 Apr 2015 07:11:46 GMT

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?

Re: How to Sort based on Measure Expression in Pivot Table?

ankitaag — Thu, 30 Apr 2015 07:17:56 GMT

Hey,

What do you mean by measure selected in pivot table?

Re: How to Sort based on Measure Expression in Pivot Table?

Anonymous — Thu, 30 Apr 2015 13:50:24 GMT

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.

Re: How to Sort based on Measure Expression in Pivot Table?

Thu, 07 May 2015 05:51:00 GMT

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,

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

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

,1 ))))

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

It is working perfectly.