Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there! I am facing an issue with totals in a stacked bar chart.
I have a stacked bar chart with two measures which can be either positive or negative. I need to show for each value of the dimension the sum of these measures.
However, when one is positive and the other negative, qlik differences between sum of positive and sum if negative.
is there any way to avoid it? By the way, is it possible to customize the formar of the total value?
thanks in advance!
Hi, @JohnIssue
What would be the customization of the totals? If you can give examples in more detail.
To avoid negative effects, you can apply axis customization, in the case of the X axis setting the minimum value, see below:
Regarts,
Matheus
Hi
In Qlik Sense, a stacked bar chart will by default show the net sum of the measures. That is, it will add up the positive values and subtract the negative ones for each stack in the chart. If you want to show the absolute sum (ignoring the sign), you need to adjust your expression to handle this logic.
Here's how you can modify the measures to achieve this:
Create new measures: Instead of using the original measures directly, create new calculated measures that take the absolute value of your measures. For example, if your original measures are Measure1
and Measure2
, you would create new measures as follows:
Abs(Measure1)
Abs(Measure2)
Total Expression: When adding the total label in the bar chart, you might need to use an expression to calculate the total as the absolute sum of Measure1
and Measure2
. This can typically be done in the chart's properties panel where you define the total.
Sum(Abs(Measure1)) + Sum(Abs(Measure2))
Custom Formatting: To customize the format of the total value, you can use the Num()
function in Qlik Sense within your expression to format the number as you want. For example:
Num(Sum(Abs(Measure1)) + Sum(Abs(Measure2)), '#,##0.00')
This will format the number with a comma as a thousand separator and two decimal places.
Here's an example of how you can implement this in a chart expression:
// Assuming your dimension is called 'Dimension'
Sum({<Dimension>} Abs(Measure1)) + Sum({<Dimension>} Abs(Measure2))
And for the custom total label, you can use the Num()
function as described above.
Keep in mind that customizing the total label's format might not always be straightforward in the Qlik Sense UI, as it often automatically formats numbers based on the overall chart settings. If the default UI options do not meet your needs, you might have to resort to using a Qlik Sense extension that allows for more customized total labels.
Hi! Thank you for your answer.
I am not sure I understand it, but for each stacked bar I am not getting the net sum of all measures. I am getting the net sum of positives measures and the net sum of negative measures.
I am attaching an example:
This bar has two measures. The blue one, which adds up 70,1 and the other, whose value es -5,4. What I expect is that the total to the right of the bar is 70,1-5,4. Is it possible?