Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
=sum(aggr(Sum({$<[Department/No]=,Brand=,[Category/No]=,[Sub Category/No]=,[Product/No]=,UPC=,Segment=,[Sub Segment]=>}Total <[Store/No],Year> QTY_SHIP),Year,[Product/No],[Store/No]))/sum(aggr(Sum({$<[Department/No]=,Brand=,[Category/No]=,[Sub Category/No]=,[Product/No]=,UPC=,Segment=,[Sub Segment]=>}Total <Year> QTY_SHIP),Year,[Product/No]))
You need to use set analysis in both Sums.
=sum( {$<[Year]={$(max(total Year)-1)}>}aggr(Sum({$<[Year]={$(max(total Year)-1)},[Department/No]=,Brand=,[Category/No]=,[Sub Category/No]=,[Product/No]=,UPC=,Segment=,[Sub Segment]=>} Total <[Store/No],Year> QTY_SHIP),Year,[Product/No],[Store/No])) /sum({$<[Year]={$(max(total Year)-1)}>}aggr(Sum({$<[Year]={$(max(total Year)-1)},[Department/No]=,Brand=,[Category/No]=,[Sub Category/No]=,[Product/No]=,UPC=,Segment=,[Sub Segment]=>} Total <Year> QTY_SHIP),Year,[Product/No]))
Start with a simple expression, make sure it's working fine and then build it up.
sum( {$<[Year]={$(max(Year)-1)}>} QTY_SHIP) will tell you your total quantity shipped last year. Remove the '-1' to calculate current year: sum( {$<[Year]={$(max(Year))}>} QTY_SHIP). Divide one by the other to calculate your rate at whatever dimension level you need.