Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Aggr on Expression

Hello, Developers.

I need some help with Aggr on Expression.

I'm trying to get counts from multiple columns and group them by the max of the count.

Here is what I'm trying to do

Order #Item 1Item 2Item 3Item 4
1Box Blue
1Box BlueBox Red
2Cup BlueCup RedCup Green
2Cup Blue

Then the count of each order will look like this

OrderCount
11
12
23
21

Now.. I want to make it like

 

OrderCount
12
23

So I used in Straight Table:

 

AGGR(MAX(
COUNT(DISTINCT Item1) + COUNT(DISTINCT Item2) + COUNT(DISTINCT Item3) +
COUNT(DISTINCT Item4))
, Order_ID

)

1) Am I using right function and right syntax (i know at least 2nd part is not right)

2) Can you help me to correct this?

Thanks!

3 Replies
lironbaram
Partner - Master III
Partner - Master III

hi check the attch file

what you where missing is a line id beacuse each order had more then one line

Not applicable
Author

Thank you for your help.

I have one more question though.

Your aggregation makes perfect sense, but is there a way to display the aggregated # and the items of the max of the aggregation.

For example,

From the example top, can I do this? (From your attachment)

Order #Item 1Item 2Item 3 Item 4Count (Aggr)
1Box BlueBox Red-- 2 (Max of Aggr)
2Cup Blue Cup RedCup Green-3

So basically, while getting the count also get the items of the rows with largest count value.

Do I need different function for this?

lironbaram
Partner - Master III
Partner - Master III

hi check attached