Calculate growth in each Treemap Dimension when Dimension are converted from Measure

theresa_lalita — Wed, 21 Dec 2022 07:43:01 GMT

Hello Everyone!

I currently developed a treemap chart in which Dimension is converted from Measure. I would like to measure growth vs. Previous Year in each Dimension

These are the Dimension conditions:

- If percentile of net sales >=50% and percentile of purchase frequency >=50% then cluster is 'Loyal Account'

- If percentile of net sales <50% and percentile of purchase frequency >=50% then cluster is 'Promising'

- If percentile of net sales >=50% and percentile of purchase frequency <50% then cluster is 'Potential Loyalist'

- If percentile of net sales <50% and percentile of purchase frequency <50% then cluster is 'Promising'

The Dimensions are expressed in Qliksense as follows:

=Aggr(=IF(
((1/(Count(distinct TOTAL [Outlet_ID])-1)) * (Rank(-Sum(net_sales))-1))>=0.5 AND ((1/(Count(distinct TOTAL [Outlet_ID])-1)) * (Rank(-Count(purchase_frequency))-1))>=0.5, 'Loyal Account',

IF(
((1/(Count(distinct TOTAL [Outlet_ID])-1)) * (Rank(-Sum(net_sales))-1))>=0.5 AND ((1/(Count(distinct TOTAL [Outlet_ID])-1)) * (Rank(-Count((purchase_frequency))-1))<0.5, 'Promising',

IF(
((1/(Count(distinct TOTAL [Outlet_ID])-1)) * (Rank(-Sum(net_sales))-1))<0.5 AND ((1/(Count(distinct TOTAL [Outlet_ID])-1)) * (Rank(-Count(purchase_frequency))-1))>=0.5, 'Potential Loyalist',

IF(
((1/(Count(distinct TOTAL [Outlet_ID])-1)) * (Rank(-Sum(net_sales))-1))<0.5 AND ((1/(Count(distinct TOTAL [Outlet_ID])-1)) * (Rank(-Count(purchase_frequency))-1))<0.5, 'Need Attention',

NULL()
)
)
)
),[Outlet_ID]
)

Measure Expression to show number of Outlet: Count(distinct Outlet_ID)

Measure Label Expression to show growth of Outlet vs. Previous Year:

num((Sum({<Year=, [Month]=, [Year Month Name]=, [Year Quarter Name]=, [YTD Name] = {'YTD-$(=Date(Max([Year-Month]),'MMM-YYYY'))'} ,
[MTD Flag]={1}>} net_sales)
/
Sum({< Year=, [Month]=, [Year Month Name]=, [Year Quarter Name]=,[YTD Name] = {'YTD-$(=Date(Max([Year-Month]),'MMM-YYYY'))'} ,
[Previous Year MTD Flag]={1}>} net_sales))-1,'#,##0.00%')

I manage to get the Dimension and Measure Expression right, but Measure Label Expression for growth shows the same result in each Dimension:

Is there any way to show the growth that is specific to each Dimension?

Your help is really appreciated!

topic Calculate growth in each Treemap Dimension when Dimension are converted from Measure in App Development

Calculate growth in each Treemap Dimension when Dimension are converted from Measure