Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bobpants
Partner - Contributor II
Partner - Contributor II

Set expression with -= giving unexpected result

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.

2 Solutions

Accepted Solutions
sunny_talwar

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
)

 

View solution in original post

sunny_talwar

It does, I would try this

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

View solution in original post

3 Replies
sunny_talwar

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
)

 

bobpants
Partner - Contributor II
Partner - Contributor II
Author

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}

sunny_talwar

It does, I would try this

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