Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I have two tables with Time Information as Follows:
The first table has a column named Period and the dates start from 4/8/2010 till 11/23/2015
The second table has a column named Date and the dates start from 11/1/2014 till 11/23/2015
The first thing I did was change the Date name to Period and then create the calendar script as follows:
Calendar:
LOAD DISTINCT
Date ( Period) as Period,
Year (Period) as Year,
Month (Period) as Month,
Date (Monthstart (Period), 'MM - YYYY') as YearMonth,
'Q' & Ceil (Month(Period)/3 as Quarter,
Dual (Year(Period) & '-Q' & Ceil (Month(Period)/3), Year(Period) & Ceil (Month(Period)/3) as YearQrt
RESIDENT Sales;
It works pretty good, but my problem is each time a I select Feb it displays no data for information relating to the second table. So I don't know what is going wrong. Please I will appreciate any help to resolve this issue.
Hi Simon,
Do the two date fields in your tables have the same meaning? Is the Sales table referred to in your script the first table, the second table or a different table?
If the date fields mean different things (eg. Order received date, Dispatch date, Invoice date, etc.) then you can't associate them and they should each have their own master calendar rather than sharing one.
Regards
Best practice dictates that your calendar should have the entire span of dates from any table linking to it. Furthermore it should contain all dates in that span. (The probable cause of not seeing any Feb in the second table is that no dates within February corresponds between the two tables whereas for the other months there are at least one corresponding day within each month)
Here is a simple Calendar script that might do the trick for you:
MinMaxPeriod:
LOAD
Min( Period ) AS MinPer , Max( Period ) AS MaxPer
RESIDENT
Sales;
vMinPer = Peek('MinPer');
vMaxPer = Peek('MaxPer');
vNumDays = vMaxPer - vMinPer + 1;
Calendar:
LOAD DISTINCT
Date ( Period) as Period,
Year (Period) as Year,
Month (Period) as Month,
Date (Monthstart (Period), 'MM - YYYY') as YearMonth,
'Q' & Ceil (Month(Period)/3 as Quarter,
Dual (Year(Period) & '-Q' & Ceil (Month(Period)/3), Year(Period) & Ceil (Month(Period)/3) as YearQrt
;
LOAD
Date( $(vMinPer) + RecNo() - 1 ) AS Period
AUTOGENERATE
$(vNumDays);
vMinPer = Null();
vMaxPer = Null();
vNumDays = Null();
Hey Petter,
Thanks alot for the calendar script. It did do the trick. I really appreciate that thanks alot.
Simon