Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Relative Values in Bar Chart

Hi Guys,

How can I show relative values against primary dimension in the Qlik Sense Bar Chart ?

About the Data

I have a very simple data set as shown below. I have also attached spreadsheet with this sample data.

table.PNG

Problem


I am using Rep & Product as Dimensions and =Num(sum(SaleAmount) / sum(TOTAL SalAmount),'0.0%') as measure.

But I get the following Chart.

reps.PNG

What I am trying to achieve?

I want to see every bar at 100% Level so I can compare relative share against each sales rep.

Right now when I filter data at a single Sales Rep level then I get the desire bar chart but it will only show one rep because I have applied filter.


I would like the below bar for all the reps.

100 percent.PNG

Thank you in advance

19 Replies
sunny_talwar

Is your field called country or something else? I am asking this to make sure you don't have two words like My country?

Anonymous
Not applicable
Author

No, I think it should work.

Here is my formula:

if(getselectedcount(Continent)=0,'Continent',

   if(getselectedcount(Continent)=1 and getselectedcount(Pays)=0,'Pays',

     if(getselectedcount(Pays)=1 and getselectedcount(Ville)=0,'Ville',

        if(getselectedcount(Ville)=1, 'Ville'))))

And here is my measure:

Sum([quantity])/aggr(Sum([quantity]),$(=vGetCurrentLocalisationFields))*100

In a table, $(vGetCurrentLocalisationFields) diplays "Continent" but the formula does not work.

sunny_talwar

May be try this... at this point, I can only play the guessing game really... but try this out

Sum([quantity])/aggr(Sum([quantity]),$(=$(vGetCurrentLocalisationFields)))*100

Anonymous
Not applicable
Author

It worked. I've got no idea why but it worked!

Using this formulas:

Sum([quantity])/Sum(TOTAL <$(=$(vGetCurrentLocalisationFields))> [quantity])*100

Thanks a lot for your help!

And if you have a explanation on the "why" it worked, I'm all hears

sunny_talwar

I believe that the first dollar sign expansion is to bring the text and the second is to convert the text into a field name so that the expression considers it as a field rather than just any random text

stefanlei
Contributor II
Contributor II

How can I use a DrillDown instead of a variable?

Sum([quantity])/aggr(Sum([quantity]),$(=$(DrillDownDimension)))*100

sunny_talwar

Not sure I follow?

stefanlei
Contributor II
Contributor II

What I mean is:

I would like to use a DrillDown-Dimension on the Y-Axis. Is it possible to calculate the relative values using a Drilldown-Dimension.

THX

sunny_talwar

May be this

Sum([quantity])/Aggr(Sum([quantity]), $(='[' & GetCurrentField([DrillDownDimension]) & ']'))*100

stefanlei
Contributor II
Contributor II

Thank you for your help, but as I'm using Qlik Sense there is no "GetCurrentField"-function. I had to find a workaround. My solution was to create a variable and use the formula from your previous post.

Sunny Talwar &lt;span class=&quot;icon-status-icon icon-mvp&quot; title=&quot;Mvp&quot;&gt;&lt;/span&gt; schrieb:

May be try this... at this point, I can only play the guessing game really... but try this out

Sum([quantity])/aggr(Sum([quantity]),$(=$(vGetCurrentLocalisationFields)))*100

In the variable I used following expression (for a 3-step Drilldown):

If(GetSelectedCount(M2) > 0 or Count(distinct M2) = 1, 'M3', If(GetSelectedCount(M1) > 0 or Count(distinct M1) = 1, 'M2', 'M1'))


I found this formula here:

Getcurrentfield() equivalent in Qlik sense