How do I Aggr orders by unique customers

[jerry_ile]

Hi,

I am attempting to create a table that gives a topline grouping of customers by the amount of orders they have created, I can't do this in the load script as need to be able to change time periods 

It should look like this 

Frequency (Orders)	Count(distinct [Order ID])	count(distinct [Customer ID])
1	43063	43063
2	16116	8058
3	8091	2697
4	4272	1068
5	2600	520
>5	8393	731

I tried 

aggr(if(count(distinct [Order ID])<=5, Text(count(distinct [Order ID])),'>5'), [Customer ID])

aggr(if(count(distinct [Order ID])<=5, Text(count(distinct [Order ID])),'>5'), [Customer ID])	Count(distinct [Order ID])	count(distinct [Customer ID])
	82,535	56,137

but it shows as a blank, if I add customer ID as a dimension I can see that the aggr is correctly grouping the customers, but just can't get it to show in the table ?

[jerry_ile]

Reposting - Any ideas?

[Lauri · Accepted solution]

Maybe your data structure is messing it up? I just tested your formula and it works when I load very simple data:

test:
Load * Inline [
Customer ID, Order ID
1, 10
1, 20
1, 30
2, 11
2, 21
1, 40
1, 50
1, 60
3, 110
3, 120
3, 130
4, 111
4, 121
4, 131
3, 140
3, 150
3, 160
3, 170
];

And I created the same table as you:

[jerry_ile]

Yep you must be right, I'll take another look 

[jerry_ile]

ok so I've used your test data and still get the same issue? Completely clean app no joins no nothing?

If I put the customer ID as a dimension I see the formulas working but really don't know what is going wrong here 

[neelamsaroha157]

If you are using a straight table then may be you can use 'Dimension Limit' tab to limit the top 5 and then show 'others; and rename the 'Others' to '<5'.

[Lauri]

Wow, that's weird. I'm using Sense Enterprise February 2019 Patch 1. But hard to believe an older version would mess up in this way.

[jerry_ile]

I can't see a dimension limit tab, I'm using Qlik Sense.

Maybe only available in Qlik view, but seemed like a great suggestion, thanks

[jerry_ile]

Maybe it's an issue with the Desktop version.

I'm on Qlik Sense Desktop February 2019

[jerry_ile]

Oh man,

Had the aggr in measure instead of dimension so your version does work

(hangs head in shame)🙄  everything's working correctly now, and was correct to begin with just under the wrong definition.