topic Re: Set expression with -= giving unexpected result in QlikView

Set expression with -= giving unexpected result

bobpants — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I am trying to convert an expression like this:

SUM(
IF(
DateWeek = $(varLastWeek) AND
DateFY = $(varLastYear) AND
SubGroup >= 11 AND
SubGroup <= 85 AND
Branch < 1032 AND
Branch <> 40,
SalesValueGross
)
)

into a set expression, something like this:

SUM(
{$<
DateWeek = {$(varLastWeek)},
DateFY={$(varLastYear)},
SubGroup = {">=11<=85"},
Branch = {"<1032"},
Branch -= {40}
>} SalesValueGross
)

Everything works fine until the last line: Branch -= {40}

If I create the expressions line by line, the two versions of the expression give me what I expect at the same stage, until I add the "-=" at the end. I have tried changing where the "-=" line sits relative to the other lines, which sometimes changes the result but is still not the correct one.

Obviously I am doing something wrong here, but I can't figure out what. I would be grateful for some guidance.

Re: Set expression with -= giving unexpected result

sunny_talwar — Wed, 11 Sep 2019 12:34:48 GMT

Try this may be... assuming <> 40 is for SubGroup and not Branch.... and Branch condition seems to be not available in the if expression....

SUM( {$< DateWeek = {$(varLastWeek)}, DateFY={$(varLastYear)}, SubGroup = {">=11<=85"}-{40}, Branch = {"<1032"} >} SalesValueGross )

Re: Set expression with -= giving unexpected result

bobpants — Wed, 11 Sep 2019 12:49:30 GMT

Sunny,

Thanks for the quick reply, but I had simplified the original expression to make it more readable here and messed it up. I have edited the post now, in case you feel like having another look.

I think your first suggestion probably still applies: Branch = {"<1032"} - {1}

Re: Set expression with -= giving unexpected result

sunny_talwar — Wed, 11 Sep 2019 12:52:48 GMT

It does, I would try this

SUM( {$< DateWeek = {$(varLastWeek)}, DateFY={$(varLastYear)}, SubGroup = {">=11<=85"}, Branch = {"<1032"}-{40} >} SalesValueGross )