Adding a footnote to a chart of the top three origins and destinations used in its composition
Get the number of origins and create a prefix string:
'Origins (' & COUNT(DISTINCT O_SA3_NAME11) & ') : '
You must use the DISTINCT operator in the count.
Generate a list of origins ranked by the number of employed persons:
AGGR(RANK(SUM([TABLE_17.EMPLOYED_PERSONS]))
Keep an origin only if it’s in the top three ranked by the number of employed persons:
IF(AGGR(RANK(SUM([TABLE_17.EMPLOYED_PERSONS])),O_SA3_NAME11)<=3,O_SA3_NAME11)
Generate a string of the top three origins as ranked by the number of employed persons:
CONCAT(IF(AGGR(RANK(SUM([TABLE_17.EMPLOYED_PERSONS])),O_SA3_NAME11)<=3,O_SA3_NAME11),', ')
Get a prefix of ‘etc’ if there is more than three origins:
IF(COUNT(DISTINCT O_SA3_NAME11)>3,', etc', '')
You must use the DISTINCT operator in the count.
All together:
='Origins (' & COUNT(DISTINCT O_SA3_NAME11) & ') : ' & CONCAT(IF(AGGR(RANK(SUM([TABLE_17.EMPLOYED_PERSONS])),O_SA3_NAME11)<=3,O_SA3_NAME11),', ')&IF(COUNT(DISTINCT O_SA3_NAME11)>3,', etc', '')
&'; Destinations (' & COUNT(DISTINCT D_SA3_NAME11) & ') : ' & CONCAT(IF(AGGR(RANK(SUM([TABLE_17.EMPLOYED_PERSONS])),D_SA3_NAME11)<=3,D_SA3_NAME11),', ')&IF(COUNT(DISTINCT D_SA3_NAME11)>3,', etc', '')