topic Yet Another Calendar Question in QlikView

Yet Another Calendar Question

Tue, 10 Nov 2009 23:31:04 GMT

Paging all experts, I have what I hope is a simple question but it has been bugging me for a while.

I have a need to track revenue and orders over a month by day and produce a chart. Using the Day variable of the following script as the Dimension (with show all Values ticked) on a Line Chart and Accumulating the Expressions the chart works fine....but and you knew it would be there did'nt you....it take the maximum number of days for any month so February shows 31 days as do all the others. How can I get the chart to reflect the correct number of days per month.

Not all days have orders

Calendar:
LOAD date(date#('3112' & $(vCurrentYear)-1,'DDMMYYYY')+recno(),'DD/MM/YY') as "Date"
AUTOGENERATE yearend(today())-date#('3112' & $(vCurrentYear)-1,'DDMMYYYY');

LEFT JOIN (Calendar)
LOAD
*,
Year & '-' & Quarter as YearQtr,
Month & '-' & Day as MonthDay;
LOAD "Date",
Date as OrderDate,
Day(Date) as Day,
Year(Date) as Year,
Month(Date) as Month,
Date(Monthstart(Date), 'MMM-YYYY') as YearMonth,
Week(Date) as Week,
Weekday(Date) as Weekday,
'Q' & Ceil(Month(Date)/3) as Quarter
RESIDENT Calendar;

Yet Another Calendar Question

Miguel_Angel_Baeyens — Tue, 10 Nov 2009 23:59:53 GMT

Hello Tony,

You may use this temporary calendar:

TempCalendar:
LOAD
$(vFirstValidDate) + Iterno() - 1 as Num,
Date($(vFirstValidDate) + Iterno() - 1) as TempDate
AUTOGENERATE 1 WHILE $(vFirstValidDate) + Iterno() - 1 <=$(vLastValidDate);

Where vFirstValidDate and vLastValidDate are got from your data source or manually LET.

Yet Another Calendar Question

Wed, 11 Nov 2009 16:16:55 GMT

Thanks for your reply but this was still not what I wanted, The problem was as DAY is basically a list of all the days in the calendar when you plot this as a Dimension it uses the maximum and minimum to produce the range (when you check show all values) which is needed as not all days have orders.

I have however stumbled on a solution on the Presentation Tab there is a section called Dimension Limitations so I checked the Max Visible Number (1-100) and placed the following in the formula DaysInMonth

Day(MonthEnd(Date)) as DaysInMonth was added to the Calendar

It now delivers exactly what I wanted