Current and previous year

[Zwax]

Hi,

So, I need some help. I’m probably missing something, but I cannot figure out what. I have searched for a solution without success.

I need to show current month and same month last year next to each other.

Calculation is:

sum([amount]) / sum([value]) * 100

Current year can easily be solved without set analysis like this:

if([Year] = max([Year]),sum([amount]) / sum([value]) * 100,)

This will work with and without selections.

Now the issue with adding previous year. First I just used the "above" function, which worked…. until I made a selection in the frontend. I know I will need to use set analysis somehow.

I have tried the following:

Aggr({1< [Year] = {"$(=Max([Year]-1 ))"}>} sum([amount]) / sum([value]) * 100, [Year_month])

This returns zero.

If I remove the set analysis part and just write: “Aggr sum([amount]) / sum([value]) * 100, [Year_month])” it works – but naturally this will not give me previous year.

Any suggestions on how to add the set analysis par (or other ways of solving it)?

[marcus_sommer · Accepted solution]

Essentially is that aggregated measures belong to their dimension-values and couldn't be not assigned to another one. Means in your case you couldn't show aggregations for the previous year against the current year. At least not with the standard-usage.

Of course there are various ways to get such views. One might be just to show all dimensions-values side by side  and then using interrecord-functions like the above() to address any cell-reference within the chart. With more or less complexity in regard to aggr() wrappings you may consider any selections and/or excluded values.

Another way would be to add dummy-values in the script to your dimension which are not directly associated with the normal data, like:

concatenate(calendar) load 'dummy' as Year autogenerate 1;

and then you could query it in the chart, like:

if(Year = 'dummy',
    sum({< Year = {"$(=max(Year))"}>} Value) / sum({< Year = {"$(=max(Year)-1)"}>} Value),
    sum(Value))

Also possible would be to add an extra or artificial dimension within a pivot and then querying the dimensionality() to branch into different calculations like above with the dummy-query.

Especially by just a few columns it would be much easier to remove the dimensionality and replacing it with expressions, in your case maybe just these 3 ones:

sum({< Year = {"$(=max(Year))"}>} Value)
sum({< Year = {"$(=max(Year)-1)"}>} Value)
sum({< Year = {"$(=max(Year))"}>} Value) / sum({< Year = {"$(=max(Year)-1)"}>} Value)

[Zwax]

Ok. The above answer made me aware that it's not as straight forward as I have hoped.

For now I have chosen a quick fix to show what I need (created a new table on year/month in the loadscript - calculated the value and joined it by itself to get prev year). Probably need to look into this some more.

Thanks for the reply 🙂