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: 
william_ericksson
Contributor III
Contributor III

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

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.

previous using set analysis.PNG

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

1 Solution

Accepted Solutions
Anil_Babu_Samineni

May be this?

Dimension is Year

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

Previous - Above(Sum(Value),1)

Capture.PNG

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

4 Replies
william_ericksson
Contributor III
Contributor III
Author

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

sum(

       {<

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

       >}

  Value)

Anil_Babu_Samineni

May be this?

Dimension is Year

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

Previous - Above(Sum(Value),1)

Capture.PNG

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
william_ericksson
Contributor III
Contributor III
Author

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!

Anil_Babu_Samineni

Awesome!! I wonder, you found something

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)