Why there's a gap in my stacked bar?

[msmichael]

 Hi expert,

I'm creating a stacked bar chart. The idea is, we have 2017 inventory of our reports for each team, we also have 2018 inventory details for each team, we want to show in the bar chart,

the 1st bar shows total number of reports in 2017

the 2nd bar shows number of reports newly created but breakdown by each team

the 3rd bar shows number of reports decommissioned (no breakdown)

the 4th bar shows total number of reports in 2018.

The bar is like a bridge, so the 2nd bar should have a offset value equals to the 1st bar height. The 3rd bar should have a offset value equals to the 4th bar height.

So my chart looks like below,

However, as you can see, there's a gap in the 2nd bar, how can I make them together?

I attached my qvw file for your convenience. 

thanks,

Michael

[sunny_talwar · Accepted solution]

Try this as your bar offsetting expression

=If(RowNo() = 1, aggr(nodistinct if(Order1='2', v2017, if(Order1='3', vDecommBarOffset)), Group1))

[msmichael]

Hi Stalwarl,

It works perfectly. But would you please explain more why it works and why mine did not work? 

Thanks,

Michael

[msmichael]

Hi Stalwarl,

I have another question on the same bar chat. How can I make the 2nd bar sort by numeric value? I do choose "Numeric Value" in the Sort tab for the 2nd dimension, but seems it's not working..

[sunny_talwar]

Hey Micheal - 

It would be easy to show the issue by converting the chart into a straight table. You expression was giving this

See how the 526 is next to Portfolio and not ASIA. For the offsetting to work properly, you would want the first value of the second dimension to be offset. But in your case it was offsetting a value from the middle.

In order to fix this, I used this to get 526 on all rows

=aggr(nodistinct if(Order1='2', v2017, if(Order1='3', vDecommBarOffset)), Group1)

Now to show this value on the first value of the second dimension... I just added If(RowNo() = 1...

=If(RowNo() = 1, aggr(nodistinct if(Order1='2', v2017, if(Order1='3', vDecommBarOffset)), Group1))

Does this help you understand what I did?

Best,
Sunny

[sunny_talwar]

I am not sure if this is possible or not.... but do you want ascending or descending order?

[msmichael]

I want the largest number to be at the bottom... Is that possible? 

[msmichael]

Hi Sunny,

Thank you so much for the crystal clear explanation! It's very helpful. I appreciate it!

Thanks again!!

Michael

[sunny_talwar]

Seems to work except the for Others

[msmichael]

Hi

That's good enough, may I know how to make it?

Thanks,

Michael