Qlik Tricks – What is selected?

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', '')

Comments are closed.