# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
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 -.

Thanks!

1 Solution

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

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

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