Measure uses changing variables

jonaguada · ‎2024-12-21

I have a P&L type need (shown below), In this example I show two calcs with two amount areas, but in reality is much larger.

	InvoiceAmt	Addons
Total	sum(InvoiceAmount)	sum(AddOns)
Average	Average(InvoiceAmount)	Average(AddOns)

The formula for each cell will be slightly different. I am using variables to fill in the formula.

cell 1,1 is sum(InvoiceAmount) - I created the variable =Pick(1 $(vExpr1) )

cell 2,1 is avg(InvoiceAmount) - I created the variable =Pick(2 $(vExpr1) )

cell 1,2 is sum(AddOns) - I created the variable =Pick(1 $(vExpr2) )

cell 2,2 is avg(AddOns) - I created the variable =Pick(2 $(vExpr2) )

where

vExpr1 = sum(InvoiceAmount), Avg(InvoiceAmount)

vExpr2 = sum(AddOns), Avg(AddOns)

I created a table in the script to assign the expressions to a row-column combination

SalesColumnID	SalesColumn	SalesRowID	SalesRow	Formula Used In PIVOT measure	ResultingFormula
1	InvoiceAmt	1	Total	Pick(SalesColumnID $(vExprSalesRowID) )	Pick(1 $(vExpr1) )
1	InvoiceAmt	2	Average		Pick(2 $(vExpr2) )
2	AddOn	1	Total		Pick(1 $(vExpr1) )
2	AddON	2	Average		Pick(2 $(vExpr2) )

The crux of my problem is that Qlik does not like variable expansion $(vExpr) at all.

Does anyone have a better method of accomplishing this? I looked at the P&L Pivot Custom Chart but that seem to only ingest resulting values. What am I missing?

Kushal_Chawda · ‎2024-12-21

@jonaguada Create a dimension with value list

=ValueList('Total','Average')

Then create two measures

// Invoice Amount
Pick(match(ValueList('Total','Average'),'Total','Average'),
sum(InvoiceAmount),
Avg(InvoiceAmount))

// Addons
Pick(match(ValueList('Total','Average'),'Total','Average'),
sum(AddOns),
avg(AddOns))

Variables