Skip to main content
Announcements
Introducing Qlik Answers: A plug-and-play, Generative AI powered RAG solution. READ ALL ABOUT IT!
cancel
Showing results for 
Search instead for 
Did you mean: 
theresa_lalita
Contributor
Contributor

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

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:

theresa_lalita_0-1671608463631.png

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

Your help is really appreciated!

 

 

Labels (1)
0 Replies