Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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
Honored Contributor II

Re: Aggr on Expression

hi check the attch file

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

Not applicable

Re: Aggr on Expression

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
Honored Contributor II

Re: Aggr on Expression

hi check attached

Community Browser