topic Re: Last year set analysis in App Development

Last year set analysis with max year

agustapia — Sat, 16 Nov 2024 21:40:23 GMT

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.

Re: Last year set analysis

sunny_talwar — Mon, 31 Dec 2018 20:44:00 GMT

So, essentially when you use this in a text box

=Max({<TipoMov = {'Actual'}>} SalesYear) - 1

you see 2017, but when you use this

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

You still see the Sales for 2018? Is that what the issue is? or do you not see anything at all?

Re: Last year set analysis

agustapia — Mon, 31 Dec 2018 20:46:06 GMT

I see nulls

Re: Last year set analysis

sunny_talwar — Mon, 31 Dec 2018 20:48:19 GMT

Are you making selection in another date or time related field which contradicts your set analysis? If you are, then ignore that field in your set analysis like this

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

Re: Last year set analysis

agustapia — Mon, 31 Dec 2018 20:51:53 GMT

No, in fact this particular view is for historical daily sales, then there is no filter selected

Re: Last year set analysis

sunny_talwar — Mon, 31 Dec 2018 20:53:15 GMT

I see nothing wrong with your expression... would you be able to share a sample so that we can see what might be going on?

Re: Last year set analysis

agustapia — Mon, 31 Dec 2018 20:56:37 GMT

Since this is classified info I cannot share a quick sample, but i can create a small app with the problem in it so you can take a look at it, only problem is I would be able to do it until wednesday, how can I get back to you when it is done?

Re: Last year set analysis

agustapia — Wed, 02 Jan 2019 17:35:32 GMT

Hi again Sunny,

Attached you can find an app with my issue. Something I couldn't realize with my full app (due to the lenght of the data and the complexity of my formulas) is that the formula is indeed working but the "Date" dimension is preventing data to be displayed.

A workaround I thought of was to add month/day dimensions, remove date dimension and add set expressions to budget and actuall figures, however that would only work for the latest year...

My last resort is to load budget only to the latest actual sales date, but if anyone ever wants to see future butget regardless of not having sales to compare to, I'd have to add it and -again- last year figures might not work.

Thanks for your support!

Re: Last year set analysis

sunny_talwar — Wed, 02 Jan 2019 18:22:47 GMT

Can you point to me as to what exactly is the issue here?

Re: Last year set analysis

agustapia — Wed, 02 Jan 2019 18:25:13 GMT

In the first row and last year column, for example, i'd expect to see 11,685, which is the actual value of 31/12/2017, this is last year's value

Re: Last year set analysis

sunny_talwar — Wed, 02 Jan 2019 18:26:30 GMT

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

Re: Last year set analysis

sunny_talwar — Wed, 02 Jan 2019 18:27:03 GMT

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

Re: Last year set analysis

agustapia — Fri, 04 Jan 2019 18:34:42 GMT

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

Re: Last year set analysis

Boucard — Fri, 19 Jul 2024 06:52:52 GMT

Can you please share the script how you did for daily?