Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
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...

Highlighted
Partner
Partner

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

Maybe is just a typo.

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

Highlighted
Creator III
Creator III

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.

Highlighted
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

Highlighted
Creator III
Creator III

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)

Highlighted
Creator III
Creator III

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.

Highlighted
Creator III
Creator III

Thanks Vamsee. Will give it a go.

Highlighted
Partner
Partner

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