Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am trying to calcualt an expression based on a value of another expression that meets a selected criteria in QlikView. But unablet to figure out:
Benchmark State | NY | |||
State | CustCount | Sales | Expected Sales | Averages |
CA | 100 | 200 | 135 | 1.45 |
NY | 200 | 250 | 270 | 1.35 |
NJ | 300 | 300 | 405 | 1.23 |
TX | 150 | 400 | 202.5 | 2 |
WA | 250 | 350 | 337.5 | 1.87 |
I need to calculate Expected Sales based on the average of the Benchmark State selected. In this example the Benchmark State is NY. So all states should use the Average of 1.35 to calculate the Expected Sales. The formula for Expected Sales is Average * CustCount.
Hi Nitya,
see Attachment
Regards,
Antonio
I laid out the data in this way so it is easy to understand but all my Expressions have calculations. In the example Average is straight feild but in my data it is a calculated expression. So in the data set where you have Average, It is a calculated expression with the formula Average: Sum ({1}RAW_COUNT)/Sum ({1}MBR_CNT). Now for the Selected Average when I try Only({1}TOTAL Average). It is not working.
Is it because my Average is not a field but a calculated expression? How can I use the same with calculated expression?
If you want to use a already calculated Expression, use column(n) or the name of the Expression ex [Averages].
n in column(n) is the Expression number, ex for Averages use column(3)
Or you can use set analysis like this: column(1)*Sum(total{1< State={'NY'}>} Averages)
Hi Nitya,
Please find the attached application to solve your problem.
Hope this helps!
-Manideep