Show missing zeros in line chart with 2 dimensions

[Jason_Michaelides]

Hi all,

Please see the attached file and ignore the fact that my dates are not formatted as such (something to do with moving my Data table to INLINE version for uploading here...??).

In the top chart you can see that I am showing all dates even when there is no associated value. This is exactly what I want (thanks to a previous post by John W for the solution).

However, in the second chart I have added a second dimension and now the "suppress zero values" and "show all values" is not having the effect I want - where there are missing data points the line does not drop to the floor as if there was a zero value.

Can anyone help!?

Thanks,

Jason

[Jason_Michaelides]

Hi guys - does anyone have any idea about this?

Thanks,

Jason

[erichshiino]

Hi, Jason.

To solve the first problem, you basically created all dates you wanted to show on your chart.

I did the same, create all dates for all [Dimension 2]

Please, check attachment.

Hope it helps,

Erich

[Jason_Michaelides]

Sorry it's taken so long to reply to this, Erich.  Thank you for your suggestion - it's certainly a solution but it's annoying that I have to do that.  Left Joins are slow across hundreds of millions of records which is the size of the FACT table I am dealing with.

Ah well, thanks again.

Jason

[Anonymous]

Hi Jason

I am trying to plot a line graph with a dimensions and want the line graph to drop to zero when there is no value for a particular month. When I remove the second dimension of the customer I get the desired result but after adding the second dimension it just doesnt behave the it is expected.

I have been going through the above post and found a similar issue that you had faced in the past. Let me know if you have found an alternate solution for it. Can you share the qv file that you used to solve the issue in notepad as I am using a personal edition?

It will certainly help !!!

[Anonymous]

Hello Erich

I have been facing a similar issue like the one posted above. I have 2 dimension i.e. Customer and Month and I need the values to drop to zero for a particular customer for a month during which there were no tickets raised for that customer.

i get the desired result when i remove the second dimension i.e. Customer. When I add the customer as a second dimension the line graph just doesnt drop to zero.

I have used the master calendar script and linked it to the facts table.

I would really like to know the alternate solution that you have shared in this post. Since I am using a personal edition please share the script in note pad or paste in the post. Thanks in advance!!

[sasiparupudi1]

Instead of adding a dimension, may be you can do set analysis expression?

example..

Date as the dimension

expression

SUM({<[Dimension 2]={'Jo'}>}Value)

where the value could be dynamically populated using a variable

the other option is to generate combinations as suggested by Erich

hth

Sasi

[#8]

i hope this is want you check the below attachment