Calandar

[#1]

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.

[Chanty4u]

hi,

https://community.qlik.com/docs/DOC-8843

Please chk above lnk

Thanks

Suresh

[effinty2112]

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

[petter]

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();

[#5]

Hey Petter,

Thanks alot for the calendar script. It did do the trick. I really appreciate that thanks alot.

Simon