Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

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

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 6th, 7th and 8th 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.

3 Replies
Highlighted
MVP & Luminary
MVP & Luminary

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

See attached qvw


talk is cheap, supply exceeds demand
Highlighted
Not applicable

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

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.

Highlighted
Employee
Employee

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

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.