How to link two dates to one master calendar

[Anonymous]

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

[amit_saini]

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,

AS

[Peter_Cammaert]

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

[its_anandrjs · Accepted solution]

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