Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
data_RN
Creator
Creator

100% Stacked Bar chart for with a Dynamic Dimension

Hello All,

I am hoping I am missing the obvious here.

I am trying to finalize a 100% Stacked bar chart, it is working successfully with this measure:

Sum([Spend])/sum(total<[Cluster]>([Spend]))

Now of course folks want to be able to drill down into the Cluster to see products, but still using a 100% stacked bar chart.  I created a Drill-Down Master Dimension, and it works to change the dimension into the next layer (Product).  Except now, the stacked bars aren't 100% and are showing the % of the Total of Cluster.  Is there way to setup the measure to change as you drill down into the dimension?

This isn't right, but something like this:  If dimension is CLUSTER, Sum([Spend])/sum(total<[Cluster]>([Spend])), if dimension is PRODUCT, Sum([Spend])/sum(total<[Product]>([Spend]))

Is this possible?

Thanks as Always!!!

1 Solution

Accepted Solutions
Patrik_Lundblad
Employee
Employee

Hi,

It was a bit more complicated than I expected to try to write it, so i cheated and created an app with the modifier.

Here is the suggested expression, and yes it will show an error in expression editor. Just ignore it.

(Sum(Spend))/Sum(Total<[$(=Replace(GetObjectField(0),']',']]'))]> Aggr((Sum(Spend)),[$(=Replace(GetObjectField(0),']',']]'))],[$(=Replace(GetObjectField(1),']',']]'))]))

 

And attached an app with it.

 

Cheers,

Patrik.

View solution in original post

6 Replies
Patrik_Lundblad
Employee
Employee

Hi,

By just looking at your expression you could replace the Dimension with GetObjectField(0) and that should work when the dimension changes.

You could also try out the relative modifer in April 2020 release which has a UI for creating expressions for this.

Cheers,

Patrik.

 

data_RN
Creator
Creator
Author

HI Patrick,

Thanks for looking at this. I will be curious to see the new UI feature, for now, I need to try it in the existing version.

Native question, should I just directly replace it into the existing formula like this:

Sum([Spend])/sum(total<GetObject Field(0)>([Spend]))

 

Patrik_Lundblad
Employee
Employee

Hi,

Yes exactly like that. 

Although it might be Getobjectfield(1) that you need. 

What it does is to look at the hypercube for the chart and it picks the column depending on the index sent in. So 0 is the first column and 1 is the second and then puts that name into the expression. That way you don't have to actually have the name of a dimension, just the position in the chart.

In your case the stack is index 0 and the segments in the stacks is 1.

Cheers,

Patrik.

data_RN
Creator
Creator
Author

Hi Patrick, 

I am hoping I just have a syntax problem.  When I apply that formula as is, I get an syntax error.

Sum([Spend])/sum(total<GetObject Field(0)>([Spend]))

I tried fixing it and then my results are blanked out.  Any idea what I have wrong in the formula?

Sum([Spend])/sum(total(<GetObject Field(0)>([Spend])))

Thanks for any assistance 🙂

Patrik_Lundblad
Employee
Employee

Hi,

It was a bit more complicated than I expected to try to write it, so i cheated and created an app with the modifier.

Here is the suggested expression, and yes it will show an error in expression editor. Just ignore it.

(Sum(Spend))/Sum(Total<[$(=Replace(GetObjectField(0),']',']]'))]> Aggr((Sum(Spend)),[$(=Replace(GetObjectField(0),']',']]'))],[$(=Replace(GetObjectField(1),']',']]'))]))

 

And attached an app with it.

 

Cheers,

Patrik.

data_RN
Creator
Creator
Author

Thanks Patrik! It works perfectly! Thank you every so much for setting this up. I would have never figured that one out on my own 😄