Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

beck_bakytbek
Valued Contributor III

Time interval into Master Calender

Hi Folks,

i have a question, please see attached screen shot

i have :

  StartDate,                EndDate         

12.03.2017                09.09.2017            

my question is: how can i take in account this time-interval in my master calender ?

my backround does look like: 

if i select the following time intervall then my amount equal : 700

  StartDate,                EndDate          Amount

12.03.2017                09.09.2017        700

and if i select this time interval then my amount equal: 600

StartDate,                EndDate          Amount

12.03.2017                09.08.2017       600

The Mothdifferences are already calculated.

Thanks a lot

Beck

3 Replies
OmarBenSalem
Esteemed Contributor

Re: Time interval into Master Calender

What exactly do you want to do Beck? Suppose you clicked in the Month(of your calendar) Jul, what is supposed to happen?

beck_bakytbek
Valued Contributor III

Re: Time interval into Master Calender

Hi Omar Thanks a lot for you feedback,

as you see, i have my data like: StartDate and EndDate, if i build a calendar (with months: mrz, apr,mai, jun) , i want to connect my StartDate and EndDate with Master Calender, so for instance: if i select the Month: Jun; so my StartDate shold have: 12.03.2017 and EndDate: should have: 09.07.2017 and my Amount equal to : 500.

if you need more info for this issue, let me pleas know

Thanks a lot

Beck

OmarBenSalem
Esteemed Contributor

Re: Time interval into Master Calender

Maybe sthing like this (I know you can adapt it to your need):

Let varMinDate=num(MakeDate(2017,01,01));

Let varMaxDate=num(MakeDate(2017,12,31));

     

TempCalendar: 

LOAD 

$(varMinDate) + Iterno()-1 As Num, 

Date($(varMinDate) + IterNo() - 1) as TempDate 

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

     

MasterCalendar: 

Load 

  TempDate AS Date, 

  week(TempDate) As Week, 

  Year(TempDate) As Year, 

  Month(TempDate) As Month, 

  Day(TempDate) As Day, 

  date(  MonthEnd(TempDate)-1) as MonthEnd,

  Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

  WeekDay(TempDate) as WeekDay 

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar; 

table:

load MemberID, date(Date#(DateCreated, 'DD MMM YYYY')) as created,

date(Date#(DateEnded, 'DD MMM YYYY')) as ended

;

load * Inline [

MemberID,   DateCreated,   DateEnded

1,    27 Feb 2017,   31 Mar 2017

2,    27 Feb 2017,   31 Mar 2017

3,    27 Jan 2017,   31 Mar 2017

3,    27 Sep 2017,   31 Nov 2017

4,    27 Nov 2017,   31 Dec 2017

5,    27 Dec 2017,   29 Dec 2017

];

IntervalMatch (Date)

Load distinct created, ended resident table;

result:

Capture.PNG

See the attached app

Community Browser