If and set analysis in same expression

[#1]

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!

[anbu1984]

Can you post sample app

[#3]

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.

[SergeyMak · Accepted solution]

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

[jonathandienst]

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

[#6]

Yey, It works, thank you Sergey!

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