5 Replies Latest reply: May 30, 2014 3:19 AM by Suvi Ahlstedt

# If and set analysis in same expression

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 -.

Thanks!

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

Can you post sample app

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

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

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

Yey, It works, thank you Sergey!

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

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

Hi

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