Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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 )
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 )
@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!