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: 
jerry_ile
Contributor III
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])
14306343063
2161168058
380912697
442721068
52600520
>58393731

 

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
Lauri
Specialist
Specialist

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:

aggr.JPG

View solution in original post

10 Replies
jerry_ile
Contributor III
Contributor III
Author

Reposting - Any ideas?

Lauri
Specialist
Specialist

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:

aggr.JPG

jerry_ile
Contributor III
Contributor III
Author

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

jerry_ile
Contributor III
Contributor III
Author

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

Capture.PNG

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

Capture2.PNG

 

neelamsaroha157
Specialist II
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'.

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

jerry_ile
Contributor III
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

jerry_ile
Contributor III
Contributor III
Author

Maybe it's an issue with the Desktop version.

I'm on Qlik Sense Desktop February 2019

jerry_ile
Contributor III
Contributor III
Author

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.