Time interval into Master Calender

[beck_bakytbek]

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

[OmarBenSalem]

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]

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]

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:

See the attached app