topic Re: Grouping columns in pivot table, where measures contain expressions (Qlik Sense) in App Development

Grouping columns in pivot table, where measures contain expressions (Qlik Sense)

TomHollandKB — Wed, 21 Sep 2022 21:12:20 GMT

I have created a pivot table which contains account details as dimensions and "Actuals", "Forecast" and "Variance" as measures.

The measures are all calculated within expression.

I need to create 3 groups of columns, 2 of them (Current month and YTD) containing the measures listed above, and another group containing "Runrate", "Budget" and "Variance (Budget/Forecast)".

I've seen workarounds using inline tables etc, however I'm struggling to apply this to my app.

Example of desired result below, any suggestions are welcome!

Current Month

YTD

Full Year

SCOA

SCOA Description

Old GL

OldGL Description

P&L Classification

IFRS

Group Account

Actual

FC09

Variance

YTD Actual

YTD Budget

Variance to Budget

Runrate

FC09

Full Year Budget

Variance FC09 Vs Budget

Re: Grouping columns in pivot table, where measures contain expressions (Qlik Sense)

Dolphin — Thu, 22 Sep 2022 08:17:23 GMT

Hi Spiderman,

please check this post. I think this is what you need. except, that you will add them as columns, not rows:

Group Kpis by theme - Qlik Community - 1933287

Kind Regards,

Johannes

Re: Grouping columns in pivot table, where measures contain expressions (Qlik Sense)

TomHollandKB — Thu, 22 Sep 2022 12:12:11 GMT

Thank you for your response!

I've successfully created the grouped columns on the pivot table which is good progress.

However, i'm now struggling with the measure expression, please see example below:

=pick([Groups2.Measureno]
, =sum(if([Period reporting on] = date(today(),'M') -1,Amount)) - (sum(if([Period reporting on] = date(today(),'M') -1,Amount))*2)
, =sum(if(FCPeriod = date(today(),'M'),[FC09-4.Forecast]))
, =sum(if([Period reporting on] = date(today(),'M') -1,Amount)) - (sum(if([Period reporting on] = date(today(),'M') -1,Amount))*2) - sum(if(FCPeriod = date(today(),'M'),[FC09-4.Forecast]))
)

Error message i'm getting is saying i'm missing a bracket, but have been trawling through the expression and can't find an error, would you mind taking a look?

Error in expression : ')' expected

Thanks again for your help!

Re: Grouping columns in pivot table, where measures contain expressions (Qlik Sense)

TomHollandKB — Thu, 22 Sep 2022 12:32:19 GMT

Have spotted the error, I needed to remove the '=' from the expressions.

Just working on the rest of the expressions will mark as solved once complete

Thanks again!