Need help in set analysis expression

[#1]

Hi,

I need help in writing specific formulae related with set analysis. I have kept attached file QlikView & input file as well for reference.

I need to create a table with dimension values + sales measure + recurring measure + % mix measure.

recurring measure formulae = if sales for that item has happened last year (i.e. last year sale >0) as well as this year (i.e. this year sales > 0) then recurring = 1 other wise 0. this is working fine.

% Mix formulae = I need help in this. I am using below formulae

Sum({$<YEAR = {$(=Max(YEAR)-1)}>} SALES)

/

Sum(TOTAL {$<YEAR = {$(=Max(YEAR)-1)}>} SALES)

Here we need to calculate percentage based on: last year sales value / total last year sales. But total last year sales should only come only when recurring = 1. This I am unable to implement how to put it only for recurring = 1.

Any help in this regard will appreciate.

Regards,

Manash

[salto]

Hi,

Column(2) will give you the result of the column in the second postiion of the chart. So:

=If (Column(2)='1',

Sum({$<YEAR = {$(=Max(YEAR)-1)}>} SALES)

/

Sum(TOTAL {$<YEAR = {$(=Max(YEAR)-1)} >} SALES))

HTH.

[#3]

Hi,

Actually I need to implement this in 2nd line.

Sum(TOTAL {$<YEAR = {$(=Max(YEAR)-1)} >} SALES))

It should do the total of those records where Recurring = 1.

Regards,

Manash