topic Re: Calculate Against First Value? in QlikView

Calculate Against First Value?

Tue, 11 Feb 2014 20:58:32 GMT

Hiya Guys,

I have a need to be able to calculate the "Price Index" of a Product (which is how much it was sold for over several months - compared to it's starting price in the first month selected).

Therefore, if the dates selected were Jan-Apr 2013 (Year_Month >= 201301 and Year_Month <= 201304), I want to be able to compare the selling price of Feb, Mar, Apr, against what the Product was sold for in Jan.

The formula I am currently using is:

=Avg({$<Year_Month={201301}, Product={Product1}>} Price)

However, this doesn't work as it only gives the Average price for the first selected month, eg:

Year_Month	Product	Price	First Month Price
201301	Product1	18,456.78	18456.78
201302	Product1	17,198.50	-
201303	Product1	18,528.82	-
201304	Product1	17,623.13	-

(in the above example I was expecting to see the "Jan Price" in each row - therefore enabling me to compare that against the "current month price").

This sounds like a job for Set Analysis - but I can't seem to get it to work.

Does anyone have any ideas?

Cheers,

Steve.

Re: Calculate Against First Value?

Nicole-Smith — Tue, 11 Feb 2014 21:03:12 GMT

How about:

FirstSortedValue(total <Product> Price, Year_Month)

Example file also attached.

Re: Calculate Against First Value?

Tue, 11 Feb 2014 21:06:17 GMT

Try this:

=Avg({$<Year_Month={201301}, Product={Product1}>} TOTAL Price)

Re: Calculate Against First Value?

Fri, 14 Feb 2014 12:50:39 GMT

Thanks Nicole and Paul!

You both pointed me in the right direction to find a solution, although unfortunately I was unable to get either of your suggestions to work.

Nicole: My Set Expression is actually a lot more complex than the example - and, unfortunately, I was unable to get the "FirstSortedValue" function to even return a result! More likely my fault than yours, and thank you for the .qvd example!

Paul: Unfortunately by using "TOTAL" it seemed to return the Average of EVERYTHING, not just the first selected month (ie: (18,456.78 + 17,198.50 + 18,528.82 + 17,623.13) / 4 = 17,951.81 from the example, above).

The expression which I have now come-up with is using the "Aggr" function:

=Aggr(Avg({$<Year_Month={201301}, Product={Product1}>} TOTAL Price, Product, Year_Month)

This gives me the desired result:

Year_Month	Product	Price	First Month Price
201301	Product1	18,456.78	18456.78
201302	Product1	17,198.50	18456.78
201303	Product1	18,528.82	18456.78
201304	Product1	17,623.13	18456.78

Many thanks to both of you for pointing me in the right direction!

Cheers,

Steve.

Re: Calculate Against First Value?

Fri, 14 Feb 2014 13:56:35 GMT

Awesome! Great to hear.

In addition, if you wanted to make the above expression more dynamic, you should be able to do something like:

Aggr(Avg({$<Year_Month={"'=min(Year_Month)"},.....))

This will always select either the min (or max) month in the data or the earliest month in the range selected. It might help you as you continue to load more data.

Thanks for posting your final solution!