% of Total Across multiple years per entity

[basildur]

Hello,

How do I calculate in set analysis:

Total of a Month (across all the years) / Total of all month (across all the years)

for each individual entity ?

As in Sum(Jan 2015 + Jan 2016 + Jan 2017 etc..) / Sum(All Month 2015 + All Month 2016 + All Month 2017 etc...) but this should be per Entity.

I know that I need to use AGGR and SUM and TOTAL in some way, but it doesn't seem to work. What I am getting is the % from the grand total of the whole table, rather then just per single month per single entity?

Any suggestions?

Thanks,

VB

[lironbaram]

hi 

do you mean you want to calculate something like 

sum(total <Month,Product> Sales)/sum(total <Product> Sales) 

this is for example the calculation for product but this function you need to include both Month and Product as dimensions in you table 

[basildur]

Hi Lironbaram, this actually worked and is closer to what I want. 

Do you think it is possible to modify this to calculate only over last 3 years ? Our upsell team would like to use it as a sort of seasonality indicator. So if we are in 2018 calculate this over 15/16/17, if 2019 over 16/17/18 etc...

Thanks

[saniyask]

Hi,

May be this will work.

Sum({<Year={'*'},Month{'Jan'}>}total Sales)
/
Sum({<Year={'*'}>}total Sales)

Regards,

Saniya.

[basildur]

Hi, this is pretty obvious, I would rather go with something like Sum({$<Year={$(=Max(Year)-3)}>} .... but not sure how to do it correctly

[lironbaram · Accepted solution]

hi Yes 

you can add set analysis to this argument 

for the last 3 years in your data you can use :

sum({<Year={">=$(=max(year)-2)"}>}total <Month,Product> Sales) 

/

sum({<Year={">=$(=max(year)-2)"}>}total <Product> Sales) 

for the previous 3 year from the current year you can use 

sum({<Year={">=$(=year(today())-3) <=$(=year(today())-1) "}>}total <Month,Product> Sales) 

/

sum({<Year={">=$(=year(today())-3) <=$(=year(today())-1) "}>}total <Product> Sales) 

[basildur]

Thanks Lironbaram, this is exactly what I need, as eventually I will not be showing the Sales per year, but rather just use the calculation you provided as a Seasonality percentage! Excellent!
Much appreciated!