topic Re: Rolling 12 month rangesum problem in App Development

Rolling 12 month rangesum problem

Akakaboto — Fri, 02 Apr 2021 07:49:00 GMT

Hello Everyone.

I have a data set streching from 2009-2019.

I am trying to create a graph with our inventory turnover currently defined as:

-sum(aggr(rangesum(above(sum([3345232000 Net Sales, total]),0,12)),[Business region], [Sub region], YearMonth))/1000
/
(
sum(aggr(rangesum(above(sum([1510000000 Inventories]),0,3)),[Business region], [Sub region], YearMonth))/3/1000
)

So basically it takes 12 month Net sales / 3 month average inventory value.

Now to my problem:

The first 12 month will always show incorrect values because of my rangesum formula. If I were to selct 2018-01 to 2018-12 in my YearMonth filter only 2018-P12 would be correct so for that reason I would have to select 2017-01 to 2018-12 to have 2018 values correct.

how can I re-write this to look at max YearMonth selected and then 12 month back with correct values for all month.

Monthnum is my dimension for month serial number...So I want to look at max YearMonth selected in my filter and then calculate 12 month back based on Monthnum.

Re: Rolling 12 month rangesum problem

sunny_talwar — Thu, 21 Feb 2019 13:23:59 GMT

May be this

-Sum(Aggr(RangeSum(Above(Sum({<YearMonth>} [3345232000 Net Sales, total]), 0, 12)), [Business region], [Sub region], YearMonth))/1000
/
(
Sum(Aggr(RangeSum(Above(Sum({<YearMonth>} [1510000000 Inventories]), 0, 3)), [Business region], [Sub region], YearMonth))/3/1000
)

Re: Rolling 12 month rangesum problem

Akakaboto — Thu, 21 Feb 2019 13:58:08 GMT

@sunny_talwar wrote:

May be this

-Sum(Aggr(RangeSum(Above(Sum({<YearMonth>} [3345232000 Net Sales, total]), 0, 12)), [Business region], [Sub region], YearMonth))/1000
/
(
Sum(Aggr(RangeSum(Above(Sum({<YearMonth>} [1510000000 Inventories]), 0, 3)), [Business region], [Sub region], YearMonth))/3/1000
)

Thank you Sir!