line in combo chart is displayed wrong for zero values

[#1]

I have a combo chart with bars in two colors for arriving and departuring cars per city. The sum of both should be displayed as a line, but if the sum is zero, the line does not show the right value. If I uncheck the option "Suppress Zero-Values", the line is at the right position. But in addition there are displayed lots of cities which do not have any values for arrived and departed cars. And I do not want to see those cities.

The picture i attached shows the wrong red line and with blue how the line should look like.

I hope anybody can help!!

[#2]

can you post the application

do you have supress zero values on the presentation tab - what about supress null values

if the line is the total, wouldnt the stacked bars represent the total?  I do not understand the negative numbers

[#3]

I attached a simple case of the application and unchecked the option "suppress zero-values". But as you can see, the cities C and F (which do not have any bars) are displayed which i do  not want.

To make the negative numbers clear: it is an application about traffic planning. I have a fleed of busses which do several trips one after the other. if one bus arrives at one city and does not have any following trips, it has a positive count of 1. if there is a trip which starts in a city and it is not clear which vehicle should serve that trip (and from which city the vehicle arrived before) than it has a negative count of -1.

In the diagram i want to show bars for the sum of both, the number of vehicles which arrive and do not have any following trips (# arrivals) and the number of trips where it is not clear, which vehicle could do that (# Departures). And for the total number i want to show a line. I get this correctly unchecking "Suppress Zero-Values", but in addition there are displayed cities with no bars (and with all my data there are more cities with no bars than cities with bars)

[#4]

I am still a little confused - I have updated the qvw you attached to have a side by side comparrision of the chart suppressing 0 values and one that does not.

If you can clarify a little further, I will try to assist if I can

[#5]

I did the same before and I understood what is happening suppressing the 0 values:

- If i suppress the 0 values, the cities with no bars disappear (in my example C and E). This behaviour is what i want to achieve. But in addition the line (which represents the totals) is wrong for city D (because the sum of 1 + (-1) is 0) because that 0 value is suppressed too and the line goes from the value 2 from B to the value 1 from F. And that behaviour is NOT what i want to happen.

- If i do not suppress the 0 values, the line for city D is at the correct position (which is 0). This is the behaviour which I want to have. But in addition in this case the citites C and E are displayed too though they do not have any bar and I do not want them to appear.

Do you think, it is possible to achieve the desired behaviour? I have already tried to replace the zeros from C and E with null values, but as i calculate the sum, I always get zero for C and E.

[Mark_Little]

Hi,

The problem is because you are supressing zero's so it is surpressing all zeros, thus the line graph is ignoring the zero value and drawing as shown.

The only way i can think is a calculated dimension something like

=If(AGGR(sum(FABS(Count)),City) <> 0, City)

Then label as city.

Hope this helps

Mark

[morganaaron · Accepted solution]

Hi Dorothea,

A temporary fix would be to use a calculated dimension like:

=If(Aggr(Sum({<Movement = {'Arrived'}>} Count), City) > 0 or Aggr(Sum({<Movement = {'Departed'}>} Count), City) < 0, City)

To exclude Cities with no Arrivals or Departures. I'd recommend instead though looking at your data, and changing this to better suit your requirements. Specifically for this, why have you got zero entries under arrivals and departures for given cities? Are these just set records for each city?

[#8]

Thank you, the solution is nearly perfect. In reality my dimension is a drill-down-group and with your solution I am not sure how to enable the updrilling.

And to answer your question concerning zero entries: No these are not just set records for each city. The use case is a bit more complex and as I mentioned above those numbers only give an information about the connection of vehicles.

If I have for example several trips which go from A to B, from B to D and from D to C and their departure- and arrival times are fine, I only need one vehicle to perform all these trips. If the arrivale time in C is about 2 pm, I could use this vehicle to perform other trips. So it has the value 1 in C. For A, B and D there are no values for this vehicle, but to display a schedule I need the data though.

I hope I could clarify the use case even better now.