Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Partner
Partner

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

Re: Set expression with -= giving unexpected result

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

It does, I would try this

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

Re: Set expression with -= giving unexpected result

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
)

 

Partner
Partner

Re: Set expression with -= giving unexpected result

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

It does, I would try this

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