topic Use of Variable in Connectivity & Data Prep

Use of Variable

Upali_Wijearatne — Tue, 12 Dec 2023 04:58:22 GMT

I have following Variable in QV document

vSale=Sum({<Type={'SA'}>}Sales)

I want to use that variable in the following expression in my Pivot Table

sum({<PERIODID={"<=$(=Max(PERIODID)-1)"},YEAR =,QUARTER =,MONTH =,PERIOD =>}$(vSale))

But When I write the expression it returns Zero Value. Kindly help me to rectify it

Re: Use of Variable

kaushiknsolanki — Tue, 12 Dec 2023 06:05:57 GMT

Hi, you can not use Sum function within Sum function, for that you need to use the Aggr function.

https://help.qlik.com/en-US/sense/November2023/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/aggr.htm#:~:text=Aggr()%20returns%20an%20array,calculated%20once%20per%20dimensional%20value.

Re: Use of Variable

BrunPierre — Tue, 12 Dec 2023 06:27:04 GMT

Try debugging the expression step by step.

Create a text object for $(vSale) to ensure that the variable is calculated correctly. Make sure that the field names in the set expressions are correct and exist in your data model.

Re: Use of Variable

Upali_Wijearatne — Tue, 12 Dec 2023 06:32:56 GMT

Thanks for Your reply. However I am new to qlikview I can not figure out how write my expression' Kindly rewrite the expression to suit my requirement

Thanks

Re: Use of Variable

MayilVahanan — Tue, 12 Dec 2023 07:06:18 GMT

Try like below

Method 1:

VariableName:vSale
Value: Sales [Without equal sign]

sum({<PERIODID={"<=$(=Max(PERIODID)-1)"},YEAR =,QUARTER =,MONTH =,PERIOD =, Type={'SA'}>}$(vSale))

or

VariableName: vTypeSA
Value: Type={'SA'} [Without equal sign]

sum({<PERIODID={"<=$(=Max(PERIODID)-1)"},YEAR =,QUARTER =,MONTH =,PERIOD =, $(vTypeSA)>}Sales)

Both will work. If you want to use like ur variable, you need to use like blelow

sum({<PERIODID={"<=$(=Max(PERIODID)-1)"},YEAR =,QUARTER =,MONTH =,PERIOD =, Type={'SA'}>} Aggr($(vSale), urdimension))

However, i think, its not optimistic.