Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Nov. 30th, Webinar! Do More With Qlik Cloud Catalog & Lineage: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
chibi
Creator
Creator

Do I need to create a variable?

Im trying to find the variance of this calculation : (Sum(tkt_amt) - sum(pay_or_cancel_amt))/count(session_id_unique)

Based on the users changes in a set analysis. Example of the set analysis I have that works for variance on a simple sum calculation (this is to find the yearly/monthly/weekly variance) 

Sum({$<[month]={"$(=Date(AddMonths(Max(month), -1), 'WW-MMM-YY'))"}>} tkt_amt) -
Sum({$<[month]={"$(=Date(Max(month), 'WW-MMM-YY'))"}>} tkt_amt)/
Sum({$<[month]={"$(=Date(AddMonths(Max(month), -1), 'WW-MMM-YY'))"}>} tkt_amt)

How do I include this calculation: (Sum(tkt_amt) - sum(pay_or_cancel_amt))/count(session_id_unique)

into this calculation

Sum({$<[month]={"$(=Date(AddMonths(Max(month), -1), 'WW-MMM-YY'))"}>} tkt_amt) -
Sum({$<[month]={"$(=Date(Max(month), 'WW-MMM-YY'))"}>} tkt_amt)/
Sum({$<[month]={"$(=Date(AddMonths(Max(month), -1), 'WW-MMM-YY'))"}>} tkt_amt)

I assume it would be easier if the calculation above is already defined like a dimension in master item but I cant include measures master item inside an expression. Can someone help me please 

Labels (1)
1 Solution

Accepted Solutions
kKoumoundouros
Partner - Contributor III
Partner - Contributor III

It seems in your variable you do not have the "="  symbol on front of your expression , so change your variable with the below:

(Sum(tkt_amt) - sum(pay_or_cancel_amt))/count(session_id_unique)

View solution in original post

7 Replies
kKoumoundouros
Partner - Contributor III
Partner - Contributor III

Hello,

As I can understand the issue is that tkt_amt is calculated from:

Sum({$<[month]={"$(=Date(AddMonths(Max(month), -1), 'WW-MMM-YY'))"}>} tkt_amt) -
Sum({$<[month]={"$(=Date(Max(month), 'WW-MMM-YY'))"}>} tkt_amt)/
Sum({$<[month]={"$(=Date(AddMonths(Max(month), -1), 'WW-MMM-YY'))"}>} tkt_amt)

and you need to calculate the below expression:

(Sum(tkt_amt) - sum(pay_or_cancel_amt))/count(session_id_unique) that tkt_amt based on the result of the first expression?

If is the case create a variable for the calculation of the tkt_amt and then use the variable on your final expression like below :

(Sum($(varialbe_name)) - sum(pay_or_cancel_amt))/count(session_id_unique)

chibi
Creator
Creator
Author

Its actually the other way round. I need this (Sum(tkt_amt) - sum(pay_or_cancel_amt))/count(session_id_unique)  to be replaced with tkt_amt  in this variance calculation. 

Sum({$<[month]={"$(=Date(AddMonths(Max(month), -1), 'WW-MMM-YY'))"}>} tkt_amt) -
Sum({$<[month]={"$(=Date(Max(month), 'WW-MMM-YY'))"}>} tkt_amt)/
Sum({$<[month]={"$(=Date(AddMonths(Max(month), -1), 'WW-MMM-YY'))"}>} tkt_amt)

So do I create a variable for this calculation (Sum(tkt_amt) - sum(pay_or_cancel_amt))/count(session_id_unique) ?

chibi
Creator
Creator
Author

I created the variable but there is no value showing. Where am I going wrong? 

vSpS = (Sum(tkt_amt) - sum(pay_or_cancel_amt))/count(session_id_unique)

Sum({$<[month]={"$(=Date(AddMonths(Max(month), -1), 'WW-MMM-YY'))"}>} vSpS) -
Sum({$<[month]={"$(=Date(Max(month), 'WW-MMM-YY'))"}>} vSpS)/
Sum({$<[month]={"$(=Date(AddMonths(Max(month), -1), 'WW-MMM-YY'))"}>} vSpS)

 

chibi_0-1634197270524.png

 

kKoumoundouros
Partner - Contributor III
Partner - Contributor III

Try to change vSpS to $(vSpS ) on your expression:

 

Sum({$<[month]={"$(=Date(AddMonths(Max(month), -1), 'WW-MMM-YY'))"}>} $(vSpS )) -
Sum({$<[month]={"$(=Date(Max(month), 'WW-MMM-YY'))"}>} $(vSpS ))/
Sum({$<[month]={"$(=Date(AddMonths(Max(month), -1), 'WW-MMM-YY'))"}>} $(vSpS ))

chibi
Creator
Creator
Author

I'm getting this error 

chibi_0-1634197615465.png

 

kKoumoundouros
Partner - Contributor III
Partner - Contributor III

It seems in your variable you do not have the "="  symbol on front of your expression , so change your variable with the below:

(Sum(tkt_amt) - sum(pay_or_cancel_amt))/count(session_id_unique)

chibi
Creator
Creator
Author

It worked! Thank you so much!