Exclusion in Set Analysis

[#1]

HI All

I am currently using this set analysis for calculating Netsales in a Straight Table

SUM( AGGR((Sum({<MONTH={'$(vThismonth)'}>}NETSALE))

now i need to exclude credits, with flag 1

exclude CR = 1

how to achieve this

i tried this but result not as expected

SUM(

  AGGR(

  (

  Sum({<MONTH={'$(vThismonth),CR -={1}'}>}NETSALE))

Please Suggest

[Anonymous · Accepted solution]

separate the expressions

SUM(

  AGGR(

  (

  Sum({<MONTH={'$(vThismonth)'},CR -={1}>}NETSALE))

[sunny_talwar]

May be try this:

=Sum(Aggr(Sum({<MONTH={'$(vThismonth)'}, CR -={1}}>}NETSALE)))

or

=Sum({<MONTH={'$(vThismonth)'}, CR -={1}'} Aggr(Sum({<MONTH={'$(vThismonth)'}, CR -={1}}>}NETSALE)))

UPDATE: Although I would suggest using just this:

Sum({<MONTH={'$(vThismonth)'}, CR -={1}}>}NETSALE)

and then use the total mode option available on the expression tab of straight table properties and set it to sum

[#4]

HI

its showing error in the expression at the values are coming as 0

[sunny_talwar]

The line underneath the curly bracket is not an error, it just says that the expression editor doesn't understand the syntax. This is fixed in the more recent version of QlikView. Are you using a older version of QlikView?

Also have you made sure that there is data except 1 for the CR field? Would you be able to share some sample data?

[Anonymous]

Qlikview Shows an error, but still performs the Expression

I use it often, it works

just ignore the red line

[#7]

HI

actually this is the complete Expression

SUM(

  AGGR(

  (

  Sum({<MONTH={'$(vThismonth)'}, CC -={1}}>}NETSALE)

  /

  Only({<MONTH={'$(vThismonth)'}>}WORKINGDAYS)

  )

  ,BRANCH)

so the rest of the expression becoming inactive

[marcus_malinow]

Looks like you have an extra curly bracket after the {1}.

As you're not specifying a dimension to apply the AGGR to, try this:

Sum({<MONTH={'$(vThismonth)'}, CR -={1}>}NETSALE)

[Anonymous]

the rest is not becoming inactive, it only Looks so

provide two  Expressions with

Sum({<MONTH={'$(vThismonth)'}, CC -={1}}>}NETSALE

and with

Only({<MONTH={'$(vThismonth)'}>}WORKINGDAYS)

and see if the result is correct

if you want a correct syntax try this one

Sum({<MONTH={'$(vThismonth)'}, CC={*}-{1}}>}NETSALE)

[#10]

HI Rudolf

Still getting 0 as values