4 Replies Latest reply: Jun 27, 2011 5:02 PM by Leonard Short

# Using Aggr with Year-1 Set Analysis

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.
• ###### Using Aggr with Year-1 Set Analysis

You need to use set analysis in both Sums.

• ###### Re: Using Aggr with Year-1 Set Analysis
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]))

```
• ###### Re: Using Aggr with Year-1 Set Analysis

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

• ###### Using Aggr with Year-1 Set Analysis

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.