Using Aggr with Year-1 Set Analysis

[#1]

Hi,  I have the following expression that calculates a rate at various aggregated levels.  This works correctly, but I am having trouble creating the expression to perform the same calculation for year ago.  Here is the code that works correctly:

=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]))  

I have tried inserting the Year={$(=max(total Year)-1)} into the Set Analysis before & after the start of the aggr functions, but everything I have tried returns a null value.  Thanks for your input.

[danielrozental]

You need to use set analysis in both Sums.

[#3]

I did try this, and it is still returning null.

=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])) 

[danielrozental]

Start with a simple expression, make sure it's working fine and then build it up.

[#5]

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.