Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

How to Sort based on Measure Expression in Pivot Table?

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?

1 Solution

Accepted Solutions
Not applicable

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

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.

3 Replies
ankitaag
Contributor III

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

Hey,

What do you mean by measure selected in pivot table?

mov
Esteemed Contributor III

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

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.

Not applicable

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

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.

Community Browser