Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Read about the latest Qlik Community enhancements on the Community News blog!
cancel
Showing results for 
Search instead for 
Did you mean: 
agustapia
Contributor III
Contributor III

Last year set analysis with max year

Hi all,

I've been dealing with this problem for a while and I'd like you to take a look:

My data set contains Actuals and Budget figures in the same table, separated by a field named TipoMov. Since budget is for the full next year, when I add the next year budget and do a normal set analysis for actuals, last year figures are the same as this year figures:

Sum({$<SalesYear={$(=Max(SalesYear)-1)}, TipoMov = {"Actual"}>} Sales)

(In this moment, Max(SalesYear) = 2019 because of next year budget)

So I tried to add to the Max a set analysis to look only for actuals, but it didn't work:

Sum({$<SalesYear={$(=Max({<TipoMov = {'Actual'}>} SalesYear)-1)}, TipoMov = {"Actual"}>} Sales)

When I execute the prior formula in a textbox, it works as expected, but when included in the set analysis it doesn't work.

Then I tried to get the max year from the data load, and using RangeMin and Max functions, but couldn't  make it work:

Sum({$<SalesYear={$(=RangeMin(Max(SalesYear), $(vMax))-1)}, TipoMov = {"Actual"}>} Sales)

Also when I execute the prior formula in a textbox, it works as expected, but when included in the set analysis it doesn't work.

I have run out of ideas,  so I hope somebody can tell me what I am doing wrong and how to correct it.

I know in 2019 it will be fixed, but I don't want to be facing this same problem next year when I load 2020 budget.

Thanks in advance.

Labels (3)
12 Replies
sunny_talwar

Are you looking to put Last year info on the same row as the current year? If that is what the goal is... you either need Above()/Below() function or preferably use The As Of Table

View solution in original post

sunny_talwar

Yup, makes sense now... you def. need The As of Table to do this...
agustapia
Contributor III
Contributor III
Author

Yes, AsOfTable made it work , it gets trickier when using days but it did the job finally. Thanks!