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

How to calculate a Expression based on a value of another expression which matches a selected value

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 StateNY
StateCustCountSalesExpected SalesAverages
CA1002001351.45
NY2002502701.35
NJ3003004051.23
TX150400202.52
WA250350337.51.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.

5 Replies
antoniotiman
Master III
Master III

Hi Nitya,

see Attachment

Regards,

Antonio

Not applicable
Author

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?

stabben23
Partner - Master
Partner - Master

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)

stabben23
Partner - Master
Partner - Master

Or you can use set analysis like this: column(1)*Sum(total{1< State={'NY'}>} Averages)

manideep78
Partner - Specialist
Partner - Specialist

Hi Nitya,

Please find the attached application to solve your problem.

Hope this helps!

-Manideep