Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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.
4 Replies
danielrozental
Honored Contributor II

Using Aggr with Year-1 Set Analysis

You need to use set analysis in both Sums.

Not applicable

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]))
danielrozental
Honored Contributor II

Re: Using Aggr with Year-1 Set Analysis

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

Not applicable

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.

Community Browser