Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
jduenyas
Contributor III

If statement and Set Anaysis inequality

Hi all

In a chart, an expression using IF statement does not result the same as Set Analysis. (If statement returns accurate result)

Here are the 2 Expressions:

IF statement:

=Sum(if(TranDate>=MonthStart(today(),-2) and TranDate <=MonthEnd(today(),-2) And AcctGroup = 'Revenue', PostAmt * -1)

Set Analysis

=Sum({<TranDate={">=$(=MonthStart(today(),-2))"}, TranDate{">=$(=MonthEnd(Today(),-2))"}, AccountGroup={'Revenue')>} PostAmt * -1)

(Looking for the total for the month 2 months ago)

sample file is attached.

Can someone shed light on the mystery?

Thanks

Josh

1 Solution

Accepted Solutions
jerem1234
Valued Contributor II

Re: If statement and Set Anaysis inequality

Use this instead:

=sum({<TranDate={">=$(=monthstart(today(),-2))<=$(=monthend(today(),-2))"}, AcctGrouID= {'Revenue'}>}PostAmt*-1)

You have to specify the range in one statement and not try to reduce the field in two sets like you had when you specifed "TranDate=" twice.

Please find attached.

Hope this helps!

View solution in original post

4 Replies
gabriel_kirst
Contributor III

Re: If statement and Set Anaysis inequality

Hi!

If you use this set analysis will have the sabe result:

=sum( {<TranDate={">=$(=monthstart(today(),-2))<=$(=monthend(today(),-2))"},AcctGrouID={'Revenue'}>}PostAmt*-1)

Regards,

Gabriel

jerem1234
Valued Contributor II

Re: If statement and Set Anaysis inequality

Use this instead:

=sum({<TranDate={">=$(=monthstart(today(),-2))<=$(=monthend(today(),-2))"}, AcctGrouID= {'Revenue'}>}PostAmt*-1)

You have to specify the range in one statement and not try to reduce the field in two sets like you had when you specifed "TranDate=" twice.

Please find attached.

Hope this helps!

View solution in original post

jduenyas
Contributor III

Re: If statement and Set Anaysis inequality

Thanks Gabriel. That is correct,

Both you and Jeremiah responded with the correct answer but I was able to click Correct Answer for only one .

Thank you

jduenyas
Contributor III

Re: If statement and Set Anaysis inequality

Thanks Jeremiah. It solved my issue