topic Re: Calandar in QlikView

Calandar

Mon, 23 Nov 2015 12:35:34 GMT

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.

Re: Calandar

Chanty4u — Mon, 23 Nov 2015 17:04:30 GMT

hi,

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

Please chk above lnk

Thanks

Suresh

Re: Calandar

effinty2112 — Mon, 23 Nov 2015 21:09:55 GMT

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

Re: Calandar

petter — Mon, 23 Nov 2015 23:19:06 GMT

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

Re: Calandar

Tue, 24 Nov 2015 07:58:23 GMT

Hey Petter,

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

Simon