topic Re: Variables and expressions in QlikView

Variables and expressions

Anonymous — Sat, 16 Jan 2016 13:07:39 GMT

I have the following set expressions:

Num(sum({<[Date Dispensed]={">=$(=addmonths(MonthEnd(max([Date Dispensed]))+1,-12)) <=$(=max([Date Dispensed]))"}>}[MedAid Amount] + Shortfall), '$##,##0.00')

I have set the above as a variable named: vSetMATCurrentDollar

and

Num(sum(Total{<[Date Dispensed]={">=$(=addmonths(MonthEnd(max([Date Dispensed]))+1,-12)) <=$(=max([Date Dispensed]))"}>}[MedAid Amount] + Shortfall), '$##,##0.00')

which has set as a variable named: vSetMATCurrentDollarTotal

In my application i now need to get the relative values of the two as follows:

$(vSetMATCurrentDollar)/$(vSetMATCurrentDollarTotal)

The above does not give the required results but if i use the two expressions as:

Num(sum({<[Date Dispensed]={">=$(=addmonths(MonthEnd(max([Date Dispensed]))+1,-12)) <=$(=max([Date Dispensed]))"}>}[MedAid Amount] + Shortfall), '$##,##0.00') / Num(sum(Total{<[Date Dispensed]={">=$(=addmonths(MonthEnd(max([Date Dispensed]))+1,-12)) <=$(=max([Date Dispensed]))"}>}[MedAid Amount] + Shortfall), '$##,##0.00')

The above gives the expected results.

Where am i getting it wrong in the use of variables above?

Regards.

Re: Variables and expressions

swuehl — Sat, 16 Jan 2016 13:26:07 GMT

Remove the label of your expression

$(vSetMATCurrentDollar)/$(vSetMATCurrentDollarTotal)

in a straight table, then hover with the mouse over the expression label in the chart, you should see the expression with all dollar sign expansions expanded. Do you see

Num(sum({<[Date Dispensed]={">=$(=addmonths(MonthEnd(max([Date Dispensed]))+1,-12)) <=$(=max([Date Dispensed]))"}>}[MedAid Amount] + Shortfall), '$##,##0.00') /Num(sum(Total{<[Date Dispensed]={">=$(=addmonths(MonthEnd(max([Date Dispensed]))+1,-12)) <=$(=max([Date Dispensed]))"}>}[MedAid Amount] + Shortfall), '$##,##0.00')

or something else?

Re: Variables and expressions

maxgro — Sat, 16 Jan 2016 13:30:26 GMT

do the variables work when you use them in 2 different expressions (2 columns)?

I tried in a .qvw and the variables work, see attachment.

Re: Variables and expressions

sunny_talwar — Sat, 16 Jan 2016 13:44:00 GMT

I think def. try both the variables separately as two expressions, but also see what do you get when you use the variables without the dollar sign expansion:

vSetMATCurrentDollar/vSetMATCurrentDollarTotal

Re: Variables and expressions

Anonymous — Sat, 16 Jan 2016 13:51:40 GMT

Thank you very much. I managed to locate my problem. I had an = sign before the vSetMATCurrentDollarTotal expression when i set my variable. This should not be there.

Regards.

Re: Variables and expressions

Anonymous — Sat, 16 Jan 2016 13:52:29 GMT

Thanks Maxgro, see my post above.

Re: Variables and expressions

Anonymous — Sat, 16 Jan 2016 13:53:26 GMT

Hi Sunny, thank you very much, see my post above, managed to locate my mistake

Re: Variables and expressions

sunny_talwar — Sat, 16 Jan 2016 13:55:11 GMT

Awesome, I am glad you were able to figure it out