Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
infock12
Creator III
Creator III

Expression in variable not working

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

9 Replies
neelamsaroha157
Specialist II
Specialist II

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
Not applicable

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

Maybe is just a typo.

vamsee
Specialist
Specialist

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
Creator III
Creator III
Author

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

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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
Creator III
Creator III
Author

Thanks Vamsee. Will give it a go.

Anonymous
Not applicable

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