topic Using Aggr with Year-1 Set Analysis in QlikView

Using Aggr with Year-1 Set Analysis

Mon, 27 Jun 2011 19:56:41 GMT

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

danielrozental — Mon, 27 Jun 2011 20:20:05 GMT

You need to use set analysis in both Sums.

Re: Using Aggr with Year-1 Set Analysis

Mon, 27 Jun 2011 20:30:22 GMT

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

danielrozental — Mon, 27 Jun 2011 20:34:48 GMT

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

Using Aggr with Year-1 Set Analysis

Mon, 27 Jun 2011 21:02:30 GMT

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.