3 Replies Latest reply: May 16, 2017 4:56 AM by omar bensalem RSS

    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

        • Re: Time interval into Master Calender
          omar bensalem

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

            • Re: Time interval into Master Calender
              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

                • Re: Time interval into Master Calender
                  omar bensalem

                  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