topic If statement and Set Anaysis inequality in QlikView

If statement and Set Anaysis inequality

jduenyas — Mon, 13 Oct 2014 17:49:57 GMT

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

Anonymous — Mon, 13 Oct 2014 17:57:56 GMT

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 — Mon, 13 Oct 2014 17:58:51 GMT

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!

jduenyas — Mon, 13 Oct 2014 18:05:06 GMT

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 — Mon, 13 Oct 2014 18:06:57 GMT

Thanks Jeremiah. It solved my issue