Nested If's Expression.

[johngouws]

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

[Anonymous · Accepted solution]

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.

[sasiparupudi1]

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

[johngouws]

Hi Manuel.

This is perfect, thank you.

Regards - John

[johngouws]

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