Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
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