Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
)
It does, I would try this
SUM(
{$<
DateWeek = {$(varLastWeek)},
DateFY={$(varLastYear)},
SubGroup = {">=11<=85"},
Branch = {"<1032"}-{40}
>} SalesValueGross
)
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
)
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}
It does, I would try this
SUM(
{$<
DateWeek = {$(varLastWeek)},
DateFY={$(varLastYear)},
SubGroup = {">=11<=85"},
Branch = {"<1032"}-{40}
>} SalesValueGross
)