Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Filtering an AGGR

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

ColorQuantity
1Red5
1Red10
1Blue5
2Red5
2Blue15
2Red5
2Red5
3Red5
4Red10

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

AccountSum of Red Quantity
115
215
35
410

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:

RangeCount Distinct Account
51
101
152

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

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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

View solution in original post

7 Replies
swuehl
MVP
MVP

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
Champion III
Champion III

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

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)
51
101
152

When Blue is selected:

=Aggr(Floor(Sum(Quantity),5),Color,Account) Count(Distinct Account &'|' & Color)
51
151
qliksus
Specialist II
Specialist II

You can also use Class Function

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

Exp : Count( Distinct Account)

Not applicable
Author

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
Champion III
Champion III

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)

Not applicable
Author

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

Best Regards,

Anshul Sahu