topic Re: How to link two dates to one master calendar in QlikView

How to link two dates to one master calendar

Anonymous — Fri, 06 Jun 2014 07:15:03 GMT

Hello everyone,

I have a data like

Table1:

District StartDate EndDate Metered Unmetered Planned

ABC 01/Jan/2015 28/Feb/2015 20 15 10

CDE 15/Jan/2015 28/Feb/2015 15 20 10

EFG 28/Jan/2015 15/Mar/2015 40 18 15

Table2:

Sector StartDate EndDate Metered Unmetered Planned

ABC1 01/Jan/2015 28/Feb/2015 10 8 5

ABC2 01/Feb/2015 28/Feb/2015 10 7 5

CDE1 15/Jan/2015 15/Feb/2015 8 10 5

CDE2 01/Feb/2015 28/Feb/2015 7 10 5

Table3:

Street StartDate EndDate Metered Unmetered Planned

ABC1A 01/Jan/2015 28/Feb/2015 5 4 3

ABC1B 15/Jan/2015 28/Feb/2015 5 4 2

ABC2A 01/Feb/2015 28/Feb/2015 10 7 5

CDE1A 15/Jan/2015 15/Feb/2015 8 10 5

Table4:

District Sector Street

ABC ABC1 ABC1A

ABC ABC1 ABC1B

ABC ABC2 ABC2A

CDE CDE1 CDE1A

CDE CDE2

I have to use 3 different calendars for table 1 ,2 and 3 . But where i m getting trapped is I will be having two dates for each master calendar.

How can that issued be solved.

When I plotted a chart of month vs planned for sector I was getting only feb as my month because I was able to link only the end date of sector with the master calendar, How can I link two different dates to one calendar.

Please help me out as its urgent.

Thanking everyone in anticiapation

Re: How to link two dates to one master calendar

amit_saini — Fri, 06 Jun 2014 07:20:35 GMT

Hi Karunpreet,

Try this:

LET vMinDate = num(makedate(2010));

LET vMaxDate = num(today());

Datefield:

LOAD

$(vMinDate) + IterNo() -1 as Datefield

AUTOGENERATE (1)

WHILE $(vMinDate) + IterNo() -1 <= $(vMaxDate);

GenCal:

LOAD

Datefield as %DateID,

// date(Datefield) as Date,

dual(date(Datefield, 'MMM YY'), year(Datefield)*100+month(Datefield)) as MonthYear,

year(Datefield) as Year,

month(Datefield) as Month,

day(Datefield) as Day,

//week(Datefield) as Week,

week(Datefield) & ' / ' & year(Datefield) as Week,

weekday(Datefield) as Weekday

RESIDENT Datefield;

drop table Datefield;

Thanks,

Re: How to link two dates to one master calendar

Peter_Cammaert — Fri, 06 Jun 2014 07:20:50 GMT

There are a few solutions that tackle this problem.

For example, Canonical Dates. See this excellent post by Hic: http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

Re: How to link two dates to one master calendar

its_anandrjs — Fri, 06 Jun 2014 07:40:08 GMT

You have to use Cononical dates in the application to sort out this problem

http://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date