Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Partner
Partner

Group measures Pivot Table

Hi Guys,

im having a pivot table with 9 calculated Measures. I want to group these measures into 3 different groups, like in this picture.

I know that I have to add some Dimension or Measure to the Columns. But im not sure how I group them correctly. Because there is no logical relation between these measures

Pivot.png

KR,

Johann

4 Replies
Highlighted
Not applicable

Re: Group measures Pivot Table

There are different ways of tackling this, but the simplest (and assuming your data model isn't too large / complex, I would go with something like this - data islands can have impacts on caching and performance for very large applications, but I won't go into it here):

Load Script:

Sales:

Load * Inline [

Customer,RevenueMTD,RevenueYTD,ProfitMTD,ProfitYTD

X,10,100,1,11

Y,20,200,2,21

Z,30,300,3,31

];

Expressions:

Load * Inline [

ExpressionID,ExpressionGroup,ExpressionName

1,MTD,MTD Revenue

2,MTD,MTD Profit

3,YTD,YTD Revenue

4,YTD,YTD Profit

];

Chart Expression:

Pick(ExpressionID, Sum(RevenueMTD), Sum(ProfitYTD), Sum(RevenueYTD), Sum(ProfitYTD))

18-07-2017 10-10-26.jpg

Partner
Partner

Re: Group measures Pivot Table

Hi Graeme,

thanks for your reply.

Unfortunatly the calculated measures are very complex(Set analysis + variables) so I cant model them in my script.

Not applicable

Re: Group measures Pivot Table

The logic for the expression above is not modelled in the script - just the name of the expression and it's grouping.

The logic for the expression is in the chart expression (you can include whatever set analysis you like in the chart expression):

Chart Expression:

Pick(ExpressionID, Sum(RevenueMTD), Sum(ProfitYTD), Sum(RevenueYTD), Sum(ProfitYTD))

amit_shetty78
Contributor II

Re: Group measures Pivot Table

Hi Graeme, thanks for this. Works nicely.

One issue is that when I collapse the expression group is there a way to do aggregation on the column included in the group? For e.g., sum all the values in the columns when collapsed.

thanks.