topic Master Calendar in QlikView

Master Calendar

Tue, 17 Aug 2010 08:23:25 GMT

Hey guys

I got a small problem. How do I add a master calendar into my document?

In the document, I've got many different dates such as DueDate,ShipDate,StartDate,EndDate, etc. All of the data needs to in fiscal format, starting from 1July and ending 30June.

Can anyone Help me create a basic calendar so that if i select a certain year, than only the data which is applicable to that date is shown?

Please let me know if i am not clear on this.

Any help is greatly appreciated.

Thanks guys

Master Calendar

Tue, 17 Aug 2010 08:24:29 GMT

Sorry but the Data needs to be added into the script so if create list boxes, than the Fiscal year works.

Master Calendar

Tue, 17 Aug 2010 10:18:40 GMT

anyone?

Master Calendar

Tue, 17 Aug 2010 12:28:43 GMT

Please find the attached QV document.

Regards,

Ravi

Master Calendar

Tue, 17 Aug 2010 12:35:21 GMT

Hey Ravi

Thanks for the reply, but I have a small problem. I got many dates for different information. Is there a way to put them all in Fiscal year or will they all automatically go in?

Master Calendar

Tue, 17 Aug 2010 12:54:29 GMT

Hi Blaze101

When you have multiple dates in your fact table that you would like to use in your application for selection purposes you need to include more calendar tables. So in your example you would require calendars for DueDate, ShipDate, StartDate, EndDate, etc.

You could use the qualify statement to avoid that you will create joins between these calendar tables (or take care of this manually).

Hope this helps

Master Calendar

Tue, 17 Aug 2010 12:56:53 GMT

Hey Keller

Thanks for the response ill try it and get back to you

Master Calendar

Tue, 17 Aug 2010 13:04:01 GMT

Hi Blaze,

If you could know the min and max date, the following code could help you:


Set Init_Date= Date('01/01/2000', 'DD/MM/YYYY');
Set Final_Date = Date('31/12/2010', '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);

I hope it helps you.

Regards.

Master Calendar

Tue, 17 Aug 2010 13:16:03 GMT

Its not working

Master Calendar

Tue, 17 Aug 2010 13:22:04 GMT

Have you tried using the variables like file attached does?

Check out your enviroment variables:

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='h:mm:ss';
SET DateFormat='DD/MM/YYYY'; // <- this could be the problem
SET TimestampFormat='DD/MM/YYYY h:mm:ss[.fff]';
SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';
SET DayNames='lun;mar;mié;jue;vie;sáb;dom';

If you have the DateFormat different from mine you have to change in the expression the right date format.

Master Calendar

Tue, 17 Aug 2010 13:30:17 GMT

I got it t work. Thanks guys. My DateFormat was incorrect. But there is another ssue, it is not linking wth the rest of the document.

Master Calendar

Tue, 17 Aug 2010 13:31:07 GMT

please find the attached Qv file.

I think this will help you.

Regards,

Ravi

Master Calendar

Tue, 17 Aug 2010 13:35:58 GMT

Just change the field name "Date" in the Calendar table for the field name whose contain the date in your data.

Check out the date format in your source data.