Hi folks,
I am trying to create a pie chart that will group companies by number of subscriptions, and combine them where the total value is < 9000.
For example, the key would be:
Number of Subscriptions:
8
7
6
5
4 or less subscriptions
Say 200 companies have 4 or less subscriptions, the total value of their combined subscriptions should be grouped together as one value on the pie chart and shown as one colour. The rest (5 to 😎 should have their values in total too, so the total value of all companies with 5 subscriptions (as say blue in the pie chart), the total value of all companies with 6 as red etc, but represented as different sections of the pie...
I am finding it difficult to figure out what my dimensions should be and expressions. This is the inline test data:
Customers:
LOAD * INLINE [ Customer_ID, Customer_Name
1, Indeedly Ltd
2, Hopably Plc
3, Hticly Ltd
4, Voticly Partnerships
5, Woulse
6, Certable
7, Vertab
8, Exreed
9, Sensly
10, Perfectly Grown Planting Co
11, Well.ly
12, Perescope
13, Howvent
14, Cachimp
15, HWHeres
16, Creaat
17, Hably
18, Justable
19, Acurew ];
Products:
LOAD * INLINE [ Product_ID, Product_Name
1, Afghanistan
2, Bahrain
3, Cambodia
4, Democratic Republic of the Congo
5, El Salvador
6, Gambia
7, Honduras
8, Indonesia
9, Japan
11, Kazakhstan
12, Laos
13, Madagascar
14, Nauru
15, Oman
16, Panama
17, Qatar
18, Rwanda
19, Saint Kitts and Nevis
20, Tajikistan
21, Uzbekistan
21, Vanuatu
21, Yemen
21, Zimbabwe ];
Subscriptions:
LOAD * INLINE [ Subscription_ID, Customer_ID, Product_ID, Value
1, 1, 1, 1200
2, 2, 1, 1200
3, 3, 1, 1200
4, 4, 1, 1200
5, 5, 1, 1200
6, 5, 2, 2400
7, 5, 3, 3600
8, 6, 1, 1200
9, 6, 2, 2400
10, 6, 3, 3600
11, 6, 4, 5700
12, 6, 5, 8000
13, 6, 6, 8150
14, 7, 1, 1200
15, 7, 2, 2400
16, 7, 3, 3600
17, 7, 4, 5700
18, 7, 5, 8000
19, 7, 6, 8150
20, 7, 7, 8275 ];
I have tried using sum(value) as the expression and this as the dimension:
=aggr(if(rank(Product_Name) <= 4, Value), Value)
This however ignores anywhere with > 4 subscriptions, but also does not combine all values where they subscribe to <= 4. I was expecting to see part of the pie chart say (blue) 69,975 (as that is the value of all accounts with <= 4 subscriptions), then another part which says 8,400 as say red, as one customer has 7 subscriptions and would not fall within the combined group...
Any help on this would be fab!
