topic Re: % of Total Across multiple years per entity in App Development

% of Total Across multiple years per entity

basildur — Sat, 16 Nov 2024 07:06:42 GMT

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,

Re: % of Total Across multiple years per entity

lironbaram — Thu, 06 Dec 2018 10:54:52 GMT

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

Re: % of Total Across multiple years per entity

basildur — Thu, 06 Dec 2018 11:00:36 GMT

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

Re: % of Total Across multiple years per entity

saniyask — Thu, 06 Dec 2018 11:02:56 GMT

Hi,

May be this will work.

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

Regards,

Saniya.

Re: % of Total Across multiple years per entity

basildur — Thu, 06 Dec 2018 11:06:30 GMT

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

Re: % of Total Across multiple years per entity

lironbaram — Thu, 06 Dec 2018 11:12:58 GMT

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)

Re: % of Total Across multiple years per entity

basildur — Thu, 06 Dec 2018 11:31:49 GMT

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!