topic Re: Set Analysis with if in QlikView

Set Analysis with if

Tue, 15 Jul 2014 13:18:11 GMT

Hi All,

I am using following expression and it doesn't work as expected. Results are totally wrong.

=sum({$<[Year]={$(=Max([Retail
Year])),$(=Max([Retail Year],2))}>}IF([ABC Flag]<>1,[SalesA],[SalesB]))

I removed the set expression and moved the sum inside the if clause which worked fine.

what I meant is

IF([ABC Flag]<>1,sum([SalesA]),sum([SalesB])) works fine. how to make above expression work?

Thanks,

Re: Set Analysis with if

alexandros17 — Tue, 15 Jul 2014 13:24:18 GMT

It should have to work, are you sure you haven't columns named [SalesA], in that cases sum takes the column not the values of the rows.

let me know

Re: Set Analysis with if

Tue, 15 Jul 2014 13:26:30 GMT

What if you evaluated the ABC Flag first like this:

IF([ABC Flag] <> 1, Sum({$<[Year]={$(=Max([Retail Year])),$(=Max([Retail Year],2))}>} [SalesA]),

Sum({$<[Year]={$(=Max([Retail Year])),$(=Max([Retail Year],2))}>} [SalesB]))

Re: Set Analysis with if

Tue, 15 Jul 2014 13:36:36 GMT

Thank you so much Lior Abitbol . your solution worked perfectly.

I am not sure what I am trying to achieve is efficient or not, do you have any other suggestions?

What is the difference between using aggregate function inside the if clause vs outside if clause?

Thanks

Re: Set Analysis with if

Tue, 15 Jul 2014 14:10:04 GMT

I don't have performance information on which approach is more efficient, but I don't think you should experience any performance difference by evaluating a condition first and calling an aggregate function (I do it this way all the time). If you want to make the expression more compact, you can define variables in the load statement for the aggregation part (especially if you are using it more than one place) and within your if statement call one of the variables based on the flag value.

Something like this:

In the load script:

SET vSumSalesA = Sum({$<[Year]={$(=Max([Retail Year])),$(=Max([Retail Year],2))}>}[SalesA]);

SET vSumSalesB = Sum({$<[Year]={$(=Max([Retail Year])),$(=Max([Retail Year],2))}>}[SalesB]);

In your UI:

IF([ABC Flag] <> 1, $(vSumSalesA), $(vSumSalesB))

Re: Set Analysis with if

rbecher — Wed, 16 Jul 2014 06:43:44 GMT

it's probably better to avoid the if( ) and do it like this:

Sum({$<[ABC Flag]-={1},[Year]={$(=Max([Retail Year])),$(=Max([Retail Year],2))}>} [SalesA]) +

Sum({$<[ABC Flag]={1},[Year]={$(=Max([Retail Year])),$(=Max([Retail Year],2))}>} [SalesB])

- Ralf

Re: Set Analysis with if

Wed, 16 Jul 2014 12:23:25 GMT

Thank you all. Appreciate your help.