# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for
Did you mean:
Contributor III

## How do I Aggr orders by unique customers

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 ?

1 Solution

Accepted Solutions
Specialist

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

test:
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:

10 Replies
Contributor III
Author

Reposting - Any ideas?

Specialist

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

test:
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:

Contributor III
Author

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

Contributor III
Author

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

Specialist II

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

Specialist

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.

Contributor III
Author

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

Contributor III
Author

Maybe it's an issue with the Desktop version.

I'm on Qlik Sense Desktop February 2019

Contributor III
Author

Oh man,