topic Re: Set analysis filter in AGGR function not working in QlikView

Set analysis filter in AGGR function not working

Wed, 25 Nov 2020 16:16:04 GMT

Greetings, Qlikview gurus!

I'm new to Qlikview and having a hard time figuring out something that is maddeningly easy in SQL Server syntax.

I have a data set where I am calculating sales totals.This expression gives me the exact grand totals I expect -

=SUM(Aggr(SUM(DISTINCT [Sales Amt]),Day, [Sales Rep Type], [Sales Rep ID], [Cust Acct Num]))

So far, so good. Incidentally, I have to use the DISTINCT clause there to compress duplicate records out of the data set.

Now, I need the report to actually show totals excluding "house accounts" (ie, sales rep ID > "0"). I've tried various methods of set analysis to get there and none of them have worked. My totals keep coming back as 0. Here's an example of one way that I laid out the syntax -

=SUM(Aggr(SUM({$<[Sales Rep ID]={">0"}>} DISTINCT [SalesAmt]),Day, [Sales Rep Type], [Sales Rep ID], [Cust Acct Num]))

That's just one example. I've tried the set analysis snippet just about everywhere possible in the expression, always getting zeros back.

By the way, the sales rep ID field is text, not a number. And yes, I'm sure that other reps besides the "house account" had sales.

What am I doing wrong? Thanks in advance!

Joel

Re: Set analysis filter in AGGR function not working

sunny_talwar — Tue, 21 Jun 2016 22:02:37 GMT

How about this:

=Sum(Aggr(Sum({$<[Sales Rep ID]={"=Num#([Sales Rep ID]) > 0"}>} DISTINCT [SalesAmt]),Day, [Sales Rep Type], [Sales Rep ID], [Cust Acct Num]))

=Sum(Aggr(Sum({$<[Sales Rep ID]={'<>0'}>} DISTINCT [SalesAmt]),Day, [Sales Rep Type], [Sales Rep ID], [Cust Acct Num]))

Re: Set analysis filter in AGGR function not working

swuehl — Tue, 21 Jun 2016 22:20:37 GMT

Some samples of field values you want to exclude and include might help to understand better what you may need here.

I guess your issue is indeed that you are coping with text values and thus a numeric search won't work well.

If you want to exclude the text value '0' and keep all other values, maybe something like

=SUM(Aggr(SUM({$<[Sales Rep ID] -={'0'}>} DISTINCT [SalesAmt]),Day, [Sales Rep Type], [Sales Rep ID], [Cust Acct Num]))

=SUM(Aggr(SUM({$<[Sales Rep ID]={"*"} - {'0'}>} DISTINCT [SalesAmt]),Day, [Sales Rep Type], [Sales Rep ID], [Cust Acct Num]))

might work. See also

Excluding values in Set Analysis

Re: Set analysis filter in AGGR function not working

Wed, 22 Jun 2016 01:49:00 GMT

Thank you so much for the help, swuehl!

I got the correct result using the "minus-equals" (-=) syntax you recommended. I did use double-quotes around the zero instead of single-quotes, but it was really the minus-equals that solved the problem.

Also, quick note for anyone reading this thread - I had to keep a space after the word "DISTINCT" for the expression to work properly.

Thanks again, swuehl! You saved what little sanity I have left!

Joel

Re: Set analysis filter in AGGR function not working

Saravanan_Desingh — Thu, 23 Jun 2016 01:03:45 GMT

I just want to mention this here, even though it is not needed in your case.

If you are using -=, this will ignore the NULLs also. If you have more than one Dim in the Set Analysis, it will throw a wrong result.

In a multi Dim Set Analysis E() can be used instead.

=SUM(Aggr(SUM({$<[SalesAmt]=E({<[Sales Rep ID] ={'0'}>})>} DISTINCT [SalesAmt]),Day, [Sales Rep Type], [Sales Rep ID], [Cust Acct Num]))

For more details see

Excluding values in Set Analysis