Display all calendar dates in a month in a straight table chart even if not measures are associated with them

[#1]

Hi All,

How can I display all the calendar dates in a specific month in a Straight Table Chart even if not measures are associated with them?

For example, the table below shows the sales and cumulative sales for product X between 1/1/2013 and 1/13/2013.

Dimensions		Metrics
Date	Product	Daily Sales	Cumulative Sales
1/1/2013	X	2	2
1/2/2013	X	3	5
1/3/2013	X	4	9
1/4/2013	X	6	15
1/5/2013	X	8	23
1/9/2013	X	9	29
1/10/2013	X	10	39
1/11/2013	X	5	44
1/12/2013	X	6	50
1/13/2013	X	8	58

However, if you look closely, you will notice that we that there are no sales for the 6^th, 7^th and 8^th of January.  Normally this would not be a problem, but my requirement is to show these missing dates with the product name (such as X in this case) and a daily sales value of zero.  Please note that I have to repeat this procedure for each product in the inventory (X,Y,Z…).

Dimensions		Metrics
Date	Product	Daily Sales	Cumulative Sales
1/1/2013	X	2	2
1/2/2013	X	3	5
1/3/2013	X	4	9
1/4/2013	X	6	15
1/5/2013	X	8	23
1/6/2013	X	0	23
1/7/2013	X	0	23
1/8/2013	X	0	23
1/9/2013	X	9	29
1/10/2013	X	10	39
1/11/2013	X	5	44
1/12/2013	X	6	50
1/13/2013	X	8	58

Thanks in advance for assisting me with this issue.

[Gysbert_Wassenaar]

See attached qvw

[#3]

Great solution, but could this be done with a straight table dynamically?

The current star schema that I'm working with only has fact and measures for the available data. If I implement this solution, I would have to add all the missing records for each day, which could be thousands if not millions.

[Josh_Good]

If you create a master calendar on your date field when you load the data and then use the expression:

rangesum(Above(total sum([Daily Sales]),0,RowNo(Total)))

you will be able to get cumulative sale for all dates included dates with no sales.

Here is how to create a master calendar.

http://community.qlik.com/thread/48693

Don't forget that QlikView only stores unique values so creating lots of rows with duplicated data won't result in big performance hits.