Filtering an AGGR

[#1]

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...

[swuehl]

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)

)

or

=SUM(

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

)

[johnw · Accepted solution]

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]))

[effinty2112]

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

[qliksus]

You can also use Class Function

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

Exp : Count( Distinct Account)

[#6]

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

[johnw]

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)

[#8]

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

Best Regards,

Anshul Sahu