topic Re: Re: Missing dates in QlikView

Missing dates

Fri, 10 Jan 2014 08:34:56 GMT

Hi,

i have seen this is a much posted about topic, but i still can't figure out from the existing discussions how to do it for my own data.

Essentially I have many rows of transaction data and I need this displayed by week on a bar chart, however there are some weeks where there are no transactions. This causes an issue as I need to still display those weeks, but with a zero value. I've read the 'Generating Missing Data.pdf' as well, but can't apply it to my own case as I'm a bit of a beginner!

As you can see, weeks commecning 16/12, 23/12 and 30/12 are absent in the chart below, but i need to see them

The script I use for my master calendar is as follows. Any help would be very much appreciated

Calendar:

LET vDateMin = Num(MakeDate(2010,7,16));

LET vDateMax = Floor(MonthEnd(Today()));

LET vDateToday = Num(Today());

TempCalendar:

LOAD

$(vDateMin) + RowNo() - 1 AS DateNumber,

Date($(vDateMin) + RowNo() - 1) AS TempDate

AUTOGENERATE 1

WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

Calendar:

LOAD

Date(TempDate) AS CalendarDate,

// Standard Date Objects

Day(TempDate) AS CalendarDayOfMonth,

WeekDay(TempDate) AS CalendarDayName,

Week(TempDate) AS CalendarWeekOfYear,

Month(TempDate) AS CalendarMonthName,

'Q' & Ceil(Month(TempDate)/3) AS CalendarQuarter,

Year(TempDate) AS CalendarYear,

// Calendar Date Names

WeekName(TempDate) as CalendarWeekNumberAndYear,

MonthName(TempDate) as CalendarMonthAndYear,

QuarterName(TempDate) as CalendarQuarterMonthsAndYear,

// Start Dates

DayStart(TempDate) as CalendarDayStart,

WeekStart(TempDate) as CalendarWeekStart,

MonthStart(TempDate) as CalendarMonthStart,

QuarterStart(TempDate) as CalendarQuarterStart,

YearStart(TempDate) as CalendarYearStart,

// End Dates

DayEnd(TempDate) as CalendarDayEnd,

WeekEnd(TempDate) as CalendarWeekEnd,

MonthEnd(TempDate) as CalendarMonthEnd,

QuarterEnd(TempDate) as CalendarQuarterEnd,

YearEnd(TempDate) as CalendarYearEnd,

// Combo Date Examples

'Q' & Ceil(Month(TempDate)/3) & '/' & Year(TempDate) AS CalendarQuarterAndYear,

Year(TempDate) & '/' & 'Q' & Ceil(Month(TempDate)/3) AS CalendarYearAndQuarter,

'Wed ' & DayStart(WeekStart(TempDate) + 3) as CalendarWednesdays

RESIDENT TempCalendar ORDER BY TempDate ASC;

DROP TABLE TempCalendar;

Re: Missing dates

dmohanty — Fri, 10 Jan 2014 08:53:45 GMT

Hi,

In chart properties, Go to Presentation Tab >> Disable the condition "Suppress Zero values"

Also in DImensions tab >> Disable Show All Values

CHeck if this helps!

Re: Missing dates

Fri, 10 Jan 2014 09:13:46 GMT

Sorry, i should also have said I only want the last 6 weeks of data.

Have played around with those settings for hours, and nothing works. The only thing which comes close, is ticking 'show all values', and having 'supress zero values' unticked, but this overrides the 6 week set analysis I am trying to do. Apart from that, it's perfect. I just need to ignore everything before 6 weeks ago and after today.

If there was a 'last' option on the dimension limits, this would be so easy!

Re: Missing dates

Nicole-Smith — Fri, 10 Jan 2014 15:07:43 GMT

Can you post a sample app with your master calendar and example data? It will be easier to help you if you do.

Re: Missing dates

chematos — Fri, 10 Jan 2014 19:11:47 GMT

Hi Alex,

I recommend you to create a resident table of your calendar only with Date field, Week field.

Table:

Load Date, Week, resident Calendar;

Then, you should do a left join of your fact table by date field:

left join(Table)

load * resident [Fact Table];

Then use limit in the chart properties

hope this helps.

Regards

Re: Re: Missing dates

Fri, 10 Jan 2014 22:03:51 GMT

Hi,

Please see the attached example which uses calculate dimension to restrict the dates.

Play around with the vNumOfDays variable and see how it changes the bottom-right chart.

http://quickdevtips.blogspot.com/

Re: Missing dates

Mon, 13 Jan 2014 09:08:38 GMT

It would be really hard for me to provide sample data, is there any point in me providing the app with no data loaded?

Re: Missing dates

Mon, 13 Jan 2014 09:18:51 GMT

The simplest solution, (although not ideal), would be to only load in the last 6 weeks date in the master calendar.

Currently my minimum date in the master calendar is expressed as follows

LET vDateMin = Num(MakeDate(2010,7,16));

How would i change this to be the first Monday of the week, 6 full weeks ago?

Re: Missing dates

Nicole-Smith — Mon, 13 Jan 2014 13:49:26 GMT

LET vDateMin = Num(WeekStart(today(),-6));

Re: Missing dates

Mon, 13 Jan 2014 13:59:13 GMT

thanks very much