Last year set analysis with max year

[agustapia]

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.

[sunny_talwar · Accepted solution]

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

[sunny_talwar]

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

[agustapia]

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