Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
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:
Reposting - Any ideas?
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:
Yep you must be right, I'll take another look
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
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'.
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.
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
Maybe it's an issue with the Desktop version.
I'm on Qlik Sense Desktop February 2019
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.