5 Replies Latest reply: Apr 25, 2017 5:48 AM by Manideep Hv

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

• ###### Re: How to calculate a Expression based on a value of another expression which matches a selected value

Hi Nitya,

see Attachment

Regards,

Antonio

• ###### Re: How to calculate a Expression based on a value of another expression which matches a selected value

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?

• ###### Re: How to calculate a Expression based on a value of another expression which matches a selected value

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)

• ###### Re: How to calculate a Expression based on a value of another expression which matches a selected value

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

• ###### Re: How to calculate a Expression based on a value of another expression which matches a selected value

Hi Nitya,