topic Re: Filtering an AGGR in QlikView

Filtering an AGGR

Wed, 25 Nov 2020 16:16:04 GMT

I'm trying to count the number of unique accounts depending on the sum of a certain color and group them by range with the data below.

Data

Account	Color	Quantity
1	Red	5
1	Red	10
1	Blue	5
2	Red	5
2	Blue	15
2	Red	5
2	Red	5
3	Red	5
4	Red	10

So I only want accounts with Red and sum the quantity:

Account	Sum of Red Quantity
1	15
2	15
3	5
4	10

So I am pretty sure this expression creates the above temporary table: AGGR(SUM(IF(Color='Red',Quantity), Account)

Then let's say I load an disconnected inline table of the "quantity ranges":

Range
5
10
15

Then I use this Range as the dimension. How do I get the expression to output the following desired outcome:

Range	Count Distinct Account
5	1
10	1
15	2

I tried...

=SUM(

IF(

AGGR(SUM(IF(Color='Red',Quantity), Account) >= Range

and

AGGR(SUM(IF(Color='Red',Quantity), Account) < Below(Range)

, 1

, 0

)

I would appreciate any help as I'm still new to all of this...

Re: Filtering an AGGR

swuehl — Thu, 19 May 2016 22:09:56 GMT

I would create a range table with RangeStart and RangeEnd fields, maybe like

LOAD Account,

Color,

Quantity

FROM

[https://community.qlik.com/thread/217613]

(html, codepage is 1252, embedded labels, table is @1);

Range:

LOAD Range

FROM

[https://community.qlik.com/thread/217613]

(html, codepage is 1252, embedded labels, table is @3);

LOAD Range, Range as RangeStart, RangeMin(Peek(Range),1000) as RangeEnd

Resident Range

Order by Range desc;

Then you can create a staight table chart with dimension Range and expression

=SUM(

AGGR(If( SUM(IF(Color='Red',Quantity))>= RangeStart and SUM(IF(Color='Red',Quantity))< RangeEnd,1,0) , Account,Range)

)

=SUM(

AGGR(If( SUM({<Color={'Red'}>} Quantity) >= RangeStart and SUM({<Color={'Red'}>} Quantity) < RangeEnd,1,0) , Account,Range)

)

Re: Filtering an AGGR

johnw — Thu, 19 May 2016 22:47:16 GMT

Attached are a few possibilities. The first one is a throw away since it uses the distinct sums as the dimension rather than ranges. It works here due to your data, but wouldn't in general.

Dimension: =aggr(sum({<[Color]={'Red'}>} Quantity),Account)

Expression: count(distinct [Account])

The second uses class() instead of defined ranges. It's not that great either.

Dimension: =class(aggr(sum({<[Color]={'Red'}>} Quantity),Account),5,'Total Red Quantity',1)

Expression: count(distinct [Account])

The third looks almost like swuehl's solution, though I was working independently. The difference is I used set analysis and I counted distinct values of account. Basically the same solution.

Dimension: Max

Expression: count(distinct aggr(if(sum({<[Color]={'Red'}>} Quantity) >= Min and sum({<[Color]={'Red'}>} Quantity) <= Max,[Account]),[Max],[Account]))

Re: Filtering an AGGR

effinty2112 — Sun, 22 May 2016 15:51:23 GMT

Hi Jason,

Try this table with the calculated dimension and expression as shown in the headings

When Red is selected:

=Aggr(Floor(Sum(Quantity),5),Color,Account)	Count(Distinct Account &'\|' & Color)
5	1
10	1
15	2

When Blue is selected:

=Aggr(Floor(Sum(Quantity),5),Color,Account)	Count(Distinct Account &'\|' & Color)
5	1
15	1

Re: Filtering an AGGR

qliksus — Tue, 24 May 2016 04:59:32 GMT

You can also use Class Function

Dimension : left(class(aggr( sum({<Color={'RED'}>}Quantity), Account),5),2)

Exp : Count( Distinct Account)

Re: Filtering an AGGR

Fri, 08 Jul 2016 07:39:36 GMT

Hi John,

I have also a kind of requirement in which "I want to count the records based on certain condition, which needs to be applied on temp table(output) of aggr " I struggled much on this but was unable to figure the same expression out. Please help me over the same.

The base expression :- aggr(count(Task) ,Name)

So it creates a virtual temporary table inside it with two columns with some name say, Count(Task) and Name

Now I want the count of those names for which the count(Task) is zero.

How can I write expression for this ?

Please suggest

Thanks and regards

Anshul Sahu

Re: Filtering an AGGR

johnw — Fri, 08 Jul 2016 16:07:09 GMT

I think this would work, and I have a bad habit of writing short, cryptic expressions like this.

-sum(aggr(count(Task)=0,Name))

But it would be more clear what the expression is intended to do if you write it like this.

count(aggr(if(count(Task)=0,Name),Name))

But you don't need to take the aggr approach. You could do it with set analysis. I'm not sure this is any more clear, though. And I'm not certain I have quite the right syntax.

count({<Name={"=(count(Task)=0)"}>} Name)

Re: Filtering an AGGR

Sun, 10 Jul 2016 14:31:35 GMT

Thank you so much John, Its working completely fine !!!!

Best Regards,

Anshul Sahu