Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Sorry but the Data needs to be added into the script so if create list boxes, than the Fiscal year works.
anyone?
Please find the attached QV document.
Regards,
Ravi
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?
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
Hey Keller
Thanks for the response ill try it and get back to you
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.
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.