Hello,
I have a requirement consisting below chart displaying Top5 customers by country:
| Country | customer percentage(ascending order) | customer count(ascending order) |
| Turkiye | 60% | 63 |
| UK | 20% | 11 |
| US | 5% | 13 |
| .... | .... | ... |
Table displays countries with the highest distinct customer counts and percentages.
Dimension: countryName
Measure-Percentage: num(Count({$<[customer.subscriptiondate]={"<=$(vEnd)>=$(vStart)"}>}distinct [customerid])/count(total {$<[customer.subscriptiondate]={"<=$(vEnd)>=$(vStart)"}>}distinct [customerid]), '#,##0.00%')
Measure-Count: Count( {$<[customer.subscriptiondate]={"<=$(vEnd)>=$(vStart)"}>} distinct[customerid])
The requirement is :
To display customers counts by customer segments(SME, SOHO, CORP, INDIV) but, at the initial state, if there is no countryName selection, highest valued customer counts by segments should be displayed.
To illustrate with the above table, if no countryName is selected, within given date range(vEnd and vStart),
(if(isnull(getSelectedCount(countryName)), ....)
As you can see the totals of the segments should be 63 which is the highest-customer-valued country, Turkiye.
For this i did below (to illustrate for SME segments):
=if(isnull(GetFieldSelections([countryName])), max(aggr(count({$<[customer.subscriptiondate]={"<=$(vEnd)>=$(vStart)"}>*<[customer.segment] ={"SME"}>}distinct [customerid]),[countryName])),count({$<[customer.subscriptiondate]={"<=$(vEnd)>=$(vStart)"},[customer.segment] ={'SME'}>}distinct [customerid]))
But it displays not Turkiye's SME values because this formula brings "the highest valued 'SME' customer count", not the "highest valued customer count's SME value". Instead of Turkiye, it displays the highest counted SME's value, which is not even in the TOP5 list, Russia. Because Russia has the maximum "SME-customer" count. I just want to display Turkiye's SME count, which has the maximum "customer" count.
Could you please help me on this?
Regards,
