Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Master Calendar

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

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

12 Replies
Not applicable
Author

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

Not applicable
Author

anyone?

Not applicable
Author

Please find the attached QV document.

Regards,

Ravi

Not applicable
Author

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?

Not applicable
Author

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

Not applicable
Author

Hey Keller

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

Not applicable
Author

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.

Not applicable
Author

Its not workingSad

Not applicable
Author

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.