Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

karunpreet
Not applicable

How to link two dates to one master calendar

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

Tags (1)
1 Solution

Accepted Solutions
its_anandrjs
Not applicable

Re: How to link two dates to one master calendar

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

3 Replies
amit_saini
Not applicable

Re: How to link two dates to one master calendar

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
Not applicable

Re: How to link two dates to one master calendar

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
Not applicable

Re: How to link two dates to one master calendar

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