Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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

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

Thanks!

1 Solution

Accepted Solutions
sergey_maka8
Valued Contributor

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

5 Replies
anbu1984
Honored Contributor III

Re: If and set analysis in same expression

Can you post sample app

Not applicable

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.

sergey_maka8
Valued Contributor

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

MVP
MVP

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

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!)

Community Browser