topic Re: Exclusion in Set Analysis in QlikView

Exclusion in Set Analysis

Mon, 26 Oct 2015 14:06:04 GMT

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

Re: Exclusion in Set Analysis

Anonymous — Mon, 26 Oct 2015 14:07:30 GMT

separate the expressions

SUM(

AGGR(

(

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

Re: Exclusion in Set Analysis

sunny_talwar — Mon, 26 Oct 2015 14:10:44 GMT

May be try this:

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

=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

Re: Exclusion in Set Analysis

Mon, 26 Oct 2015 14:39:24 GMT

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

Re: Exclusion in Set Analysis

sunny_talwar — Mon, 26 Oct 2015 14:41:53 GMT

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?

Re: Exclusion in Set Analysis

Anonymous — Mon, 26 Oct 2015 14:42:26 GMT

Qlikview Shows an error, but still performs the Expression

I use it often, it works

just ignore the red line

Re: Exclusion in Set Analysis

Mon, 26 Oct 2015 14:47:32 GMT

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

Re: Exclusion in Set Analysis

marcus_malinow — Mon, 26 Oct 2015 14:47:48 GMT

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)

Re: Exclusion in Set Analysis

Anonymous — Mon, 26 Oct 2015 14:52:24 GMT

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)

Re: Exclusion in Set Analysis

Mon, 26 Oct 2015 15:26:13 GMT

HI Rudolf

Still getting 0 as values

Re: Exclusion in Set Analysis

marcus_malinow — Mon, 26 Oct 2015 15:32:31 GMT

You have an extra curly bracket after the CC -={1}.

SUM(

AGGR(

(

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

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

)

,BRANCH)

Re: Exclusion in Set Analysis

Anonymous — Mon, 26 Oct 2015 15:32:40 GMT

can you Show your complete expression

did you try the Expression?

AGGR(

(

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

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

)

,BRANCH)

if thisexpression results in 0

try the single expressions

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

and see the results

are the month Format identical?

Re: Exclusion in Set Analysis

Mon, 26 Oct 2015 16:24:58 GMT

HI Marcus

I tried without the extra } but not showing any Values

Re: Exclusion in Set Analysis

Mon, 26 Oct 2015 17:00:16 GMT

HI Rudolf

the Month fields are identical

when i use this below expression alone i am getting values

Re: Exclusion in Set Analysis

Anonymous — Mon, 26 Oct 2015 17:09:20 GMT

so let's summarize

you get a value when you use Sum({<MONTH={'$(vThismonth)'}, CC -={1}}>}NETSALE)

monthfield and variable are identical,

but complete Expression Returns 0

question: does your datamodel have a Connection between BRANCH and values?

is the resulting number large enough >0 (or is NETSALES/NrOfWorkingdays)<1 and Expression is defined as integer?

-> can you post example qvw?

Re: Exclusion in Set Analysis

Tue, 27 Oct 2015 13:24:34 GMT

HI Rudolf

Your expression worked, Don't know why it didn't work initially, I recreated the Straight table and it worked

might be i missed some thing

SUM(

AGGR(

(

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