Missing Months in Chart

[#1]

The image I've attached probably makes my problem evident.

This is one of our Sales persons who didn't have a sale from December to February.

My dataset from SQL only returns rows like this:

Date of Sale - Sales Person - Quantity Sold
11/01/2008 - Lindsay - 1
03/01/2009 - Lindsay - 1
03/15/2009 - Lindsay - 1
04/01/2009 - Lindsay - 1
04/01/2009 - Lindsay - 1
04/03/2009 - Lindsay - 1
04/05/2009 - Lindsay - 1
04/07/2009 - Lindsay - 1
04/11/2009 - Lindsay - 1

etc.

How can I best mimic the missing months where 0 was sold? Do I have to add something to my dataset coming from SQL? Or is there another way?

Thank you in advance.

[johnw]

Frankly, I have the same question about how to best fill in the missing months. I'll go ahead and upload one way that works. I create a calendar disconnected from my data, and then link the two fields together like this:

sum(if("The Real Date" = "My Fake Date", "My Quantity Field))

I also use a calculated dimension, like this:

if("My Fake Date">=$(=num(min("The Real Date"))) and "My Fake Date"<=$(=num(max("The Real Date"))),"My Fake Date")

On the presentation tab, make sure you don't suppress zero values. Do suppress the dimension when value is null. The problems with this approach are complexity and performance. On reports for large data sets, it slows to a crawl.

Hopefully there's a better solution.

[prieper]

Hi,

you may choose "continuous" as x-Axis, thus for each month between the first and the last activitiy there will be one entry on the x-axis. Would also propose, not to use lines, but bars in this case, as a line always gives the impression, that you might interpolate betw two intervals.

Problem in this solution is, that if you wish to show some 0-values as starter or end of an interval - in such case then the solution with a "shadow-table" is more adequate.

HTH

Peter

[#4]

It will be always good, if you can create a separate calendar table, and relate it with your sales table using the date field. See the data model of the example.

[#5]

Thank you everyone for your advice.

I tried to use continous x-axis, but that did not seem to help. It simply didn't know that my x-axis was MONTHS, and couldn't fill in the missing pattern, I guess.

Connecting my table to a seperate calendar got too complex for me.

I believe I will somehow fake the data in the dataset coming back from SQL. So that my dataset will look like:

Date of Sale - Sales Person - Quantity Sold
11/01/2008 - Lindsay - 1
12/01/2008 - Lindsay - 0
01/01/2009 - Lindsay - 0
02/01/2009 - Lindsay - 0
03/01/2009 - Lindsay - 1
03/15/2009 - Lindsay - 1
04/01/2009 - Lindsay - 1
04/01/2009 - Lindsay - 1
04/03/2009 - Lindsay - 1
04/05/2009 - Lindsay - 1
04/07/2009 - Lindsay - 1
04/11/2009 - Lindsay - 1

[#6]

Hi,

I was able to achieve the required result using the following approach.

1. Create an inline load of all the months
LOAD * INLINE [

DateofSale1, MonthName

01/01/2009, Jan

02/01/2009, Feb

...

];

2. For showing months select the dimension DateofSale1 - instead of your original DateofSale field

3. For calculating the QuantitySold - use expression 

              sum(if(DateofSale = DateofSale1, Quantity Sold))

The sample formula would work if your DateofSale is first of the month as per the inline DateofSale1 structure. For your dataset you might have to add condition to compare the year and month.

Also on the presentation tab, make sure you don't suppress zero values.

Hope that helps.