topic Nested if-else into set analysis in QlikView

Nested if-else into set analysis

Wed, 24 Jun 2009 13:05:41 GMT

Dear All,

I have following expression :-
sum(if(some condition,sales,if(some condition,sales))

I want to convert above nested if-else expression into set analysis.

simple if expression can be converted into set analysis very easily.

Thanks in advance.

Regards,
Ravish.

Nested if-else into set analysis

Wed, 24 Jun 2009 20:46:51 GMT

I'm assuming the Sales field would be different for each condition.

This should work:

Sum ({<Field1 = {1}>} Sales1) + Sum ({<Field1 = {'<1>1'}, Field2 = {1}>} Sales2)

If your values are not numeric, I think you can use this for the not equal portion:

Sum ({<Field1 = {Y}>} Sales1) + Sum ({<Field1 -= {Y}, Field2 = {Y}>} Sales2)

Nested if-else into set analysis

johnw — Thu, 25 Jun 2009 00:28:03 GMT

Yeah, it would help if we could tell if the conditions are different and/or the sales are different. As written, it's just sum(if(some condition,sales)), which is almost certainly not what you were asking about.

I'm not going to go through all four possibilities, so I'll just give you the rough idea. Set analysis has operators for union and exclusion, and some combination of these are likely what you want. Have a look at the help text for set analysis. Or give an actual example, and we can easily convert it to the set analysis equivalent.

Nested if-else into set analysis

Fri, 26 Jun 2009 10:57:27 GMT

Thanks for your help.
I want to convert following nested if-else expression into set analysis :-

=sum(if(F1='FTD',if(year=vIndexYear and Month=vIndexMonth and Day=vIndexDay,amt),
if(F1='MTD',if(year=vIndexYear and Month=(vIndexMonth) and Day<=vIndexDay,amt),
if(F1='YTD',if(year=vIndexYear and new<=m,amt)))))

so that my chart will be looked like as follows :-

F1	FTD	MTD	YTD
	200	400	700

Regards,
Ravish.

Nested if-else into set analysis

johnw — Sat, 27 Jun 2009 00:41:34 GMT

Before we even get into set analysis, you should realize that the IF statement as you wrote it is being evaluated for every row of the table, where part of it really only needs to be evaluated once. So you'd get some improvement by just moving parts of it outside of the sum:

=if(F1='FTD',sum(if(year=vIndexYear and Month=vIndexMonth and Day =vIndexDay,amt))
,if(F1='MTD',sum(if(year=vIndexYear and Month=vIndexMonth and Day<=vIndexDay,amt))
,if(F1='YTD',sum(if(year=vIndexYear and new<=m,amt)))))

And that's the basic structure I'd keep when converting to set analysis. I think the set analysis expression would be this (untested):

=if(F1='FTD',sum({<year={$(=vIndexYear)} Month={$(=vIndexMonth)} Day={$(=vIndexDay)}>}amt)
,if(F1='MTD',sum({<year={$(=vIndexYear)} Month={$(=vIndexMonth)} Day={'<=$(=vIndexDay)'}>}amt)
,if(F1='YTD',sum({<year={$(=vIndexYear)} new={'<=$(=m)'}>}amt))))

Nested if-else into set analysis

Tue, 26 Jan 2010 05:33:23 GMT

Hi John

I am new to qlikview and need some help to undestand the syntaxis, I am trying to do a If..else nestled like this

*************

IF ( fe_ul_pa - vence_en>=45) then

Tota_PrecioSKU * 10;

elseif (fe_ul_pa - vence_en) >= 41 and (fe_ul_pa - vence_en <= 45) then

Tota_PrecioSKU * 0.5;

elseif (fe_ul_pa - vence_en) >= 36 and (fe_ul_pa - vence_en) <= 40 then

Tota_PrecioSKU * 0.1;

elseif (fe_ul_pa - vence_en) >= 0 and (fe_ul_pa - vence_en) <= 35 then

Tota_PrecioSKU * 0.2;

end if;

**********

buy i have some errors expression, I will appreciate your help to do in a better simple way, or correct the syntax.

thx in advanced

Nested if-else into set analysis

johnw — Tue, 26 Jan 2010 18:16:57 GMT

Assuming integer values, which is what it appears you're testing due to the integer gaps in the ranges:

Tota_PrecioSKU
*if(fe_ul_pa - vence_en >= 45, 10
,if(fe_ul_pa - vence_en >= 41, .5
,if(fe_ul_pa - vence_en >= 36, .1
,if(fe_ul_pa - vence_en >= 0, .2))))

Nested if-else into set analysis

Tue, 26 Jan 2010 22:31:42 GMT

Thanks John this solution works for me.

May I apply a sum to this result in order to get summary?

Nested if-else into set analysis

johnw — Tue, 26 Jan 2010 23:01:48 GMT

You should be able to. Just sum(that expression).