topic Re: If and set analysis in same expression in QlikView

If and set analysis in same expression

Fri, 30 May 2014 05:57:15 GMT

Hi,

I have problem with if / set analysis expression. Expression definition:

=if(CashPeriod=1 and CashCalcMark=1, sum({<Year={$(=varCurrentYear)},Month={$(=vMinMonth)}>}CashFlowAmount),

if(CashPeriod=1 and CashCalcMark=2, sum({<Year={$(=varCurrentYear)},Month={$(=vMinMonth)}>}CashFlowAmount*-1),

if(CashPeriod=2 and CashCalcMark=1, sum({<Year={$(=varCurrentYear)}>}CashFlowAmount),

if(CashPeriod=2 and CashCalcMark=2,sum({<Year={$(=varCurrentYear)}>}CashFlowAmount*-1)))))

And it returns only -.

Can someone please help me with this, I don´t know what´s wrong there.

Thanks!

Re: If and set analysis in same expression

anbu1984 — Fri, 30 May 2014 06:00:47 GMT

Can you post sample app

Re: Re: If and set analysis in same expression

Fri, 30 May 2014 06:55:18 GMT

Hi,

Actually I did nothing and now I my expression works. BUT not for totals, only row sums are correct (I mean 4th column). So my question is how I can get total sums calculated? Sorry those lines are in Finnish.

Re: Re: Re: If and set analysis in same expression

SergeyMak — Fri, 30 May 2014 07:08:03 GMT

Try this expression

sum({<Year={$(=varCurrentYear)},Month={$(=vMinMonth)},CashPeriod={1},CashCalcMark={1} >}CashFlowAmount)+

sum({<Year={$(=varCurrentYear)},Month={$(=vMinMonth)},CashPeriod={1},CashCalcMark={2}>}CashFlowAmount*-1)+

sum({<Year={$(=varCurrentYear)},Month={$(=vMinMonth)},CashPeriod={2},CashCalcMark={1}>}CashFlowAmount)+

sum({<Year={$(=varCurrentYear)},CashPeriod={2},CashCalcMark={2}>}CashFlowAmount*-1)

PFA

Re: If and set analysis in same expression

jonathandienst — Fri, 30 May 2014 07:18:33 GMT

You need to use aggr like this:

Sum(Aggr(

if(CashPeriod=1 and CashCalcMark=1,

sum({<Year={$(=varCurrentYear)}, Month={$(=vMinMonth)}>} CashFlowAmount),

if(CashPeriod=1 and CashCalcMark=2,

sum({<Year={$(=varCurrentYear)}, Month={$(=vMinMonth)}>} CashFlowAmount*-1),

if(CashPeriod=2 and CashCalcMark=1,

sum({<Year={$(=varCurrentYear)}>} CashFlowAmount),

if(CashPeriod=2 and CashCalcMark=2,

sum({<Year={$(=varCurrentYear)}>} CashFlowAmount*-1)

)

)))

,taso2nimi_Kassa

,taso3nimi_Kassa

,CashType

))

This returns -183255,46 in your uploaded example. Replace the expression you posted with this one.

HTH

Jonathan

Re: If and set analysis in same expression

Fri, 30 May 2014 07:19:21 GMT

Yey, It works, thank you Sergey!

(There is in 3rd clause also vMinMonth but when I removed it, expression works such fine!)