Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Missing Months in Chart

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.

error loading image

5 Replies
johnw
Champion III
Champion III

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
Master II
Master II

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

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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.