Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

tim_leijen
New Contributor III

And formula combined with greater than

Hi all,

Just tried to set up an expression where based on two factors QV needs to do come up with volumes.


So basically what I'm saying here, if last year we had volume (either negative or positive) and this year there is also volume (negative or positive), then deduct last years volume of this years volume.

So I created the formula below, but for some reason it doesn't take into consideration the if formula for LY and ACT. Now it's just giving me the difference between both periods.

=IF(sum({<GL_1={VOL},Dimension={LY}>}Data)<>0 AND if(sum({<GL_1={VOL},Dimension={ACT}>}Data)<>0,

sum({<GL_1={VOL},Dimension={ACT}>}Data)-sum({<GL_1={VOL},Dimension={LY}>}Data)

,0))

What am I doing wrong?

3 Replies

Re: And formula combined with greater than

Try this way?

=IF(sum({<GL_1={VOL},Dimension={LY}>}Data)<>0 AND sum({<GL_1={VOL},Dimension={ACT}>}Data)<>0,

sum({<GL_1={VOL},Dimension={ACT}>}Data)-sum({<GL_1={VOL},Dimension={LY}>}Data)

,0)

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
tim_leijen
New Contributor III

Re: And formula combined with greater than

Thanks, but didn't work.... still not taking into consideration the if part

Partner
Partner

Re: And formula combined with greater than

try to use addional brackets around the conditional expressions like

=IF((sum({<GL_1={VOL},Dimension={LY}>}Data)<>0) AND (sum({<GL_1={VOL},Dimension={ACT}>}Data)<>0),

sum({<GL_1={VOL},Dimension={ACT}>}Data)-sum({<GL_1={VOL},Dimension={LY}>}Data)

,0)

if still does not work test your expressions step by step using e.g. a textbox

IF(sum({<GL_1={VOL},Dimension={LY}>}Data)<>0,1,0) to test Last year (and maybe select a Dimension)

then test

if(sum({<GL_1={VOL},Dimension={ACT}>}Data)<>0,1,0)

both Expression smust return a 1, then you may test

=IF((sum({<GL_1={VOL},Dimension={LY}>}Data)<>0) AND (sum({<GL_1={VOL},Dimension={ACT}>}Data)<>0),

sum({<GL_1={VOL},Dimension={ACT}>}Data)-sum({<GL_1={VOL},Dimension={LY}>}Data)

,0)