topic Re: Nested If's Expression. in QlikView

Nested If's Expression.

johngouws — Fri, 03 Jun 2016 11:09:43 GMT

Good day,

I am comparing the first 2 days of the current month to the first 2 days of the previous month. I have a creation date, amount and branch.

I can show in separate columns the % change for days that are better, worse and divide by zero. I can show in 1 column a combination of 2 of these, but not all 3 in a single column. (A next step will be to also show ones with no change.)

The attached qvw has an object with all the above example, Because of the complexity, it is easier to attach the object. I would appreciate any advise regarding resolving the problem.

Thanks,

John

Re: Nested If's Expression.

Anonymous — Fri, 03 Jun 2016 11:34:03 GMT

Hi John,

This expression is working for you:

if(sum({<created_date={">=$(=AddYears(MonthStart(Today(),0),0))<=$(=AddYears(AddMonths(Today()-1,0),0))"}, disbursement_status={'Processed'} >} Amount) >0

and sum({<created_date={">=$(=AddYears(MonthStart(Today(),-1),0))<=$(=AddYears(AddMonths(Today()-1,-1),0))"}, disbursement_status={'Processed'} >} Amount)=0, 1,

(sum({<created_date={">=$(=AddYears(MonthStart(Today(),0),0))<=$(=AddYears(AddMonths(Today()-1,0),0))"}, disbursement_status={'Processed'} >} Amount)

- sum({<created_date={">=$(=AddYears(MonthStart(Today(),-1),0))<=$(=AddYears(AddMonths(Today()-1,-1),0))"}, disbursement_status={'Processed'} >} Amount))

if(sum({<created_date={">=$(=AddYears(MonthStart(Today(),0),0))<=$(=AddYears(AddMonths(Today()-1,0),0))"}, disbursement_status={'Processed'} >} Amount)

> sum({<created_date={">=$(=AddYears(MonthStart(Today(),-1),0))<=$(=AddYears(AddMonths(Today()-1,-1),0))"}, disbursement_status={'Processed'} >} Amount),

sum({<created_date={">=$(=AddYears(MonthStart(Today(),-1),0))<=$(=AddYears(AddMonths(Today()-1,-1),0))"}, disbursement_status={'Processed'} >} Amount),

sum({<created_date={">=$(=AddYears(MonthStart(Today(),0),0))<=$(=AddYears(AddMonths(Today()-1,0),0))"}, disbursement_status={'Processed'} >} Amount)))

It is showing this:

If Is Div by zero Then 1

Else (A - B)/IF IsPositive Then Positive Else Negative

Kind regards.

Re: Nested If's Expression.

sasiparupudi1 — Fri, 03 Jun 2016 11:36:39 GMT

You can simplify the calculations by referring to the column no's or column names

ex: for your expression ='Div Zero'&chr(10)&'& Negative',

if(Column(2)<Column(1), (Column(2)-Column(1))/Column(2) )

hth

Sasi

Re: Nested If's Expression.

johngouws — Fri, 03 Jun 2016 12:07:24 GMT

Hi Manuel.

This is perfect, thank you.

Regards - John

Re: Nested If's Expression.

johngouws — Mon, 06 Jun 2016 04:49:20 GMT

Good morning Sasi.

You are correct and in normal circumstances I would use column(?) numbers. In this particular case I am also showing the Country Currency, so the field becomes text and the calculation does not return a result.

Otherwise you solution also works.

Tks - John