Expression in variable not working

[infock12]

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

[neelamsaroha157]

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...

[Anonymous]

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

Maybe is just a typo.

[vamsee]

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.

[infock12]

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.

[vamsee]

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

[infock12]

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)

[infock12]

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.

[infock12]

Thanks Vamsee. Will give it a go.

[Anonymous]

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