topic Re: Two problems with my chart: weekends and continuous data in QlikView

Two problems with my chart: weekends and continuous data

Anonymous — Thu, 17 Nov 2011 20:59:51 GMT

Hello

I am trying to plot a standard line chart, X=Dates and Y=Sum(Percentages of product)

In my access table I have all the dates I would like to show on my chart as the Sum(Percentage of product)=100% every day.

So when I show all data, I have all dates and 100% for each date.

Now when I select one product, the Sum(Percentage of product) might have a data on that particular date or be empty.

My problem is that:

- if I set the X=Dates as continuous, I have all the dates (including weekends which would be nice if I could get rid of) and the Dates for which the Sum(Percentage of product) is empty will have no data and the line will continue from the last available point to the next, which is misleading.

- if I do not set the dates as continuous, the empty data will just not show. I would like them to be zero instead. Empty=zero to me.

I have attached the 2 charts which are not what I want. I would like to have all the dates on my X axes excluding weekend) and show 0 when data is missing.I hope that makes sense.

Many thanks in advance.

22/02/2011	5.9%
23/02/2011	5.9%
24/02/2011	6.0%
25/02/2011	7.0%
28/02/2011	6.8%
01/03/2011	5.9%
02/03/2011	4.5%
03/03/2011	2.5%
04/03/2011	0.0%
07/03/2011	0.0%
08/03/2011	0.0%
09/03/2011	0.0%
10/03/2011	0.0%
11/03/2011	0.0%
21/09/2011	0.0%
05/10/2011	0.4%
06/10/2011	2.7%
07/10/2011	3.2%
10/10/2011	3.5%
11/10/2011	3.5%
12/10/2011	3.6%
13/10/2011	3.4%
14/10/2011	3.6%
17/10/2011	3.6%
18/10/2011	4.5%
19/10/2011	4.4%
20/10/2011	4.4%

As you can see, the dates are only Working days, and there are period where there is no data. My problem is that I would like to do a continuous chart, by that I mean having all dates

Two problems with my chart: weekends and continuous data

Thu, 17 Nov 2011 21:10:07 GMT

You need to create a calendar in your script

It looks like

Set Init_Date= Date('01/01/2011', 'DD/MM/YYYY');

Set Final_Date = Date('31/12/2011', 'DD/MM/YYYY');

Calendar:

load Date($(Init_Date) - 1 + RecNo(), 'DD/MM/YYYY') as Date,

If(Num(Month(Date($(Init_Date) - 1 + RecNo(), 'DD/MM/YYYY'))) < 7,

Year(Date($(Init_Date) - 1 + RecNo(), 'DD/MM/YYYY')) - 1,

Year(Date($(Init_Date) - 1 + RecNo(), 'DD/MM/YYYY'))) as FiscalYear

autogenerate ($(Final_Date) - $(Init_Date) + 1);

Re: Two problems with my chart: weekends and continuous data

johnw — Thu, 17 Nov 2011 21:19:55 GMT

Unfortunately, QlikView is very poor at showing you missing data, such as dates for which you have no activity. The most common solution is probably to create a date island, a calendar of dates that is completely separate from your data. Then you tie your real data together with the date island by using if() statements in the chart. Performance is, unfortunately, really bad for large data sets

Attached is a high-performance example based on a solution by Michael Solomovich. It's unfortunately a bit complicated, particularly when applied to certain real world data models. There's a lot more information in the below thread, including an example of the date island approach.

http://community.qlik.com/message/109672

Re: Two problems with my chart: weekends and continuous data

Anonymous — Wed, 30 Nov 2011 10:14:45 GMT

Thank you very much for your quick answer.

It looks a bit complex for my knowledge so far, so I will adjust my data to fill the missing data at the source.

Many thanks.

Pierre