Bullet Chart as Percentage Bars for Drill-down Treemap

[JoshPark]

Hi everyone, 

I am using Qlik Sense Cloud and finding the solution to display bullet charts as percentage bars following corresponding treemaps, and it should change when I select different selection accordingly. 

For example, 

When I choose Year “2022”:  
- The 1st bullet chart should show (for ex.) 40%, which is the portion of Sales in 2022 over Total Sales (from 2019 until today in 2023) 
- The 2nd bullet chart should keep showing 100% 
- The 3rd bullet chart should keep showing 100% 

Then I add 2nd filter of Division “Global Account”: 
- The 1st bullet chart should keep showing 40% 
- The 2nd bullet chart should show (for ex.) 7%, which is the portion of Sales of Division “Global Account” in 2022 
- The 3rd bullet chart should keep showing 100% 

Then I add 3rd filter of City “Seoul”:  
- The 1st bullet chart should keep showing 40% 
- The 2nd bullet chart should keep showing 7%  
- The 3rd bullet chart should show (for ex.) 3%, which is the portion of Sales of City “Seoul” under Division “Global Account” in 2022 

Does anyone have any ideas?

Thank you so much!

[KGalloway]

You may consider using set analysis to ignore specific filters in each bullet chart. 

For example,

Bullet Chart 1: sum({[Global Account] =, City= >} Sales) / sum(all Sales) // ignores selections on global account and city

Bullet Chart 2: sum({City= >} Sales) / sum(all Sales) // ignores selections on city

Bullet Chart 3: sum(Sales) / sum(all Sales)

In each case, I uses the "all" keyword to ensure that when 2022 is selected, we still total sales from all years for the denominator.

[JoshPark]

Hi @KGalloway 

Thank you for your suggestion. 

As I understand, you will use fixed value (for ex. [Division] = {'Global Account'})as condition in the set analysis of function sum?

But when I change to selection other selection (for ex.) in "Division" (not only "Global Account" but also the red area), it seems that cannot change the percentage in accordance with my selection? 

So far I get it that the bullet and the treemap are all "charts" which visualize the same data, which mean the data being selected and visualized by the treemap cannot be used as data for the bullet chart. 

Do you have any idea to present?

[KGalloway]

I misunderstood part of the original post. The first formula should be 

Bullet Chart 1: sum({[Division] =, City= >} Sales) / sum(all Sales) // ignores selections on division and city

However, this still may not solve your problem.

Could you provide a small sample data (with the same schema that you are using) set so we can try to produce the results you want from that data?

[JoshPark]

Hi @KGalloway 

Please excuse me for the late reply because we have Tet Holiday in Vietnam.

Could you check the attached app for reference?

Thank you so much!

[KGalloway · Accepted solution]

This may be closer to what you're aiming for.

Bullet Chart 1: Sum({<[Region (SAP)]=, [District]=>} Sales) / (Sum(all Sales))

Once a year has been selected, this will show the sum of sales in that year divided by the sum of sales in all years. The set analysis statements on [Region (SAP)] and [District] will ensure that this chart continues to show the same thing even when values are selected in those fields.

Bullet Chart 2: if(GetSelectedCount([Region (SAP)]) > 0, sum({<[District]= >}Sales) / sum({<[Region (SAP)]= ,[District]= >} Sales), sum(Sales) / sum(Sales))

Once a value has been selected in [Region (SAP)], this chart will show the sum of Sales in that Region in 2022 divided by the sum of sales in all regions in 2022. Otherwise, it always shows 100%. The set analysis statement on [Region (SAP)] ensures the chart continues to use the sum of sales in all regions in 2022 once one or more regions are selected. The set analysis statement on [District] ensures this chart continues to show the same thing once we select values in [District].

Bullet Chart 3: if(GetSelectedCount([Region (SAP)]) > 0 and GetSelectedCount(District) > 0, sum(Sales) / sum({<[District]= >} Sales), sum(Sales) / sum(Sales))

Once a value has been selected in both [Region (SAP)] and [District], this chart will show the sum of sales in that region and district in 2022 divided by the total sales in all districts in that region in 2022. Otherwise, it always shows 100%. The set analysis statement on [District] ensures that this chart uses the sum of sales in all districts as the denominator.

The following three screenshots show the changes in the charts as I make the selections 2022, then International, then USA1 in the fields [Date.autoCalendar.Year], [Region (SAP)], and [District] respectively.

[JoshPark]

Hi @KGalloway 

Thank you so much!