topic Re: Expression in variable not working in QlikView

Expression in variable not working

infock12 — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I have the below expression and I am trying to create a variable but it does not seem to work. Any help would be appreciated.

(((sum(if(len(CANC_DTTM)<=0 ,Calc)))-(count(distinct(if(Cancellation = 'N' AND CancellationonDay <> 'Y' and FullorHalf = 'Full',Desc)))*30))-(sum(UTDif1)-(count(distinct(if(Cancellation = 'N' AND CancellationonDay <> 'Y' and FullorHalf = 'Full',Desc)))*30)))/((sum(if(len(CANC_DTTM)<=0 ,Calc)))-count(distinct(if(Cancellation = 'N' AND CancellationonDay <> 'Y' and FullorHalf = 'Full',Desc)))*30)

Thanks,

Karthik

Re: Expression in variable not working

neelamsaroha157 — Thu, 16 Aug 2018 13:30:37 GMT

Not sure why your expression is not working but I would suggest to create variables for each part of the expression and then see if each individual part works like

vVar1 sum(if(len(CANC_DTTM)<=0 ,Calc)))

vVar2 (count(distinct(if(Cancellation = 'N' AND CancellationonDay <> 'Y' and FullorHalf = 'Full',Desc)))*30))

and so on...

Re: Expression in variable not working

Anonymous — Thu, 16 Aug 2018 13:33:17 GMT

Try to test in small steps and getting slowly to the full size expression.

Maybe is just a typo.

Re: Expression in variable not working

vamsee — Thu, 16 Aug 2018 14:24:21 GMT

Hello,

I would suggest you to complete your if statement i.e. add an else clause as highlighted below

((

(sum(if(len(CANC_DTTM)<=0 ,Calc , 0)))-

(count(distinct(if(Cancellation = 'N' AND CancellationonDay <> 'Y' and FullorHalf = 'Full',Desc)))*30)

)

(sum(UTDif1)-(count(distinct(if(Cancellation = 'N' AND CancellationonDay <> 'Y' and FullorHalf = 'Full',Desc)))*30)

))

((sum(if(len(CANC_DTTM)<=0 ,Calc, 0)))-

(count(distinct(if(Cancellation = 'N' AND CancellationonDay <> 'Y' and FullorHalf = 'Full',Desc)))*30))

Thanks.

Re: Expression in variable not working

infock12 — Thu, 16 Aug 2018 15:10:21 GMT

Hi Vamsee,

Thanks for the response. When I use that in the expression, it works fine but when I add it as a variable, it gives me a fixed average. For example, if the values for M1 to 4 are 89, 78, 92, 94, it averages these and gives a single figure for M1 to 4.

Re: Expression in variable not working

vamsee — Thu, 16 Aug 2018 15:18:18 GMT

Hi Karthik,

Where are you creating these variables?

If you are planning to use these variables in charts then create them using set in the script editor or load them from excel.

If you're creating this variable using variable overview then do not use '=' before the expression.

LET, SET, Quotes | Qlikview Cookbook

Re: Expression in variable not working

infock12 — Thu, 16 Aug 2018 15:28:18 GMT

Thanks Neelam. I tried individual variables for each expression and it leads to average it to one number.

This is what I did after creating individual variables. There were 6 in total.

=Aggr((vVar1-vVar2-vVar3-vVar4), WeekEnding)/Aggr((vVar5-vVar6), WeekEnding)

Re: Expression in variable not working

infock12 — Thu, 16 Aug 2018 15:29:11 GMT

Thanks Felipe. The expression is working fine but only when I copy the expression and paste it to create a variable, it does not work.

Re: Expression in variable not working

infock12 — Thu, 16 Aug 2018 16:24:46 GMT

Thanks Vamsee. Will give it a go.

Re: Expression in variable not working

Anonymous — Thu, 16 Aug 2018 16:30:47 GMT

Try to create a variable by going to the botou left of the Qlik Sense Screen and then using the alias.

I've had trouble with variables too. And that solved it