topic Re: Using set analysis to return last year's value by month in QlikView

Using set analysis to return last year's value by month

william_ericksson — Wed, 17 May 2017 23:35:10 GMT

Hi everyone,

I've got a situation where I want to return the value of the previous year in a chart.

This is easy enough using above() function. However, I don't want to display the first year on my chart, so my selection is limited to 2015 onwards. The above() cannot refer to values that are not in the table.

Here is an example of my input and desired output.

I was thinking of using a set analysis to accomplish this, something like:

sum(

Year = {"Year - 1"}

Value)

But this doesn't work. I parameterised my year deduction and tried to insert it within $():

Variable:

expYearMinusOne = '$1-1'

Then in the chart:

=$(expYearMinusOne(Cal_Year)) returns the year minus one as expected.

However, this doesn't work within a set analysis, e.g.:

sum(

Year = {'$(expYearMinusOne(Year))'}

Value)

Any other ideas?

Thanks,

Will

Re: Using set analysis to return last year's value by month

william_ericksson — Wed, 17 May 2017 23:42:19 GMT

I should add that my final set analysis syntax was actually:

sum(

Year = {"=$(expYearMinusOne(Year))"}

Value)

Re: Using set analysis to return last year's value by month

Anil_Babu_Samineni — Thu, 18 May 2017 01:22:39 GMT

May be this?

Dimension is Year

Current - Sum({<Year -= {'$(=Min(Year))'}>}Value)

Previous - Above(Sum(Value),1)

Re: Using set analysis to return last year's value by month

william_ericksson — Thu, 18 May 2017 01:57:25 GMT

Thank you Anil -your solution works well!

So in summary - rather than INCLUDE the years I want to see using set analysis, the solution is to EXCLUDE the years not wanted using -=

Brilliant!

Re: Using set analysis to return last year's value by month

Anil_Babu_Samineni — Thu, 18 May 2017 02:00:15 GMT

Awesome!! I wonder, you found something