3 Replies Latest reply: Feb 17, 2016 4:14 AM by Sunny Talwar RSS

    Concatenation of Two Uneven Calendar tables.

    vijaykrishnamraju vegesna

      Hi everyone can one help regarding the data modelling of two different calendars.

       

      hi in my requirement i need to get all date calculations common for two different tables.

      one table have 2014,2015,2016 date and second table have 2015,2016 date only, when i do concatenation of two tables it working for reports well but when i click on the salefy it default showing the second table months only not first table months.

       

      please see the data below...

       

       

       

       

       

       

      SS_OrdersCalTemp:

      Load Min(BILLDATE) as SS_MinOrdDate,

      Max(BILLDATE) as SS_MaxOrdDate

      Resident CLOSINGSTOCKSUMMARY;

       

       

      let SS_EndDate=Date(Peek('SS_MaxOrdDate',0,'SS_OrdersCalTemp'),'$(DateFormat)');

      let SS_StartDate=Date(Peek('SS_MinOrdDate',0,'SS_OrdersCalTemp'),'$(DateFormat)');

      let SS_caldays=(SS_EndDate-SS_StartDate)+1;

      let SS_vToday=num(SS_EndDate);

       

       

      SS_Cal:

      Load RecNo() as SS_RecordNo,

      if(RecNo()=1,Date('$(SS_StartDate)'),Date(peek("SS_D")+1)) as SS_D

      autogenerate(SS_caldays);

       

       

       

       

      SS_Calendar:

      LOAD

        SS_D AS BILLDATE,

        Floor(SS_D) as NumDate,

        SS_D AS Date,

        Year(SS_D) AS Year,

        Month(SS_D) AS Month,

        week(SS_D) AS Week,

        weekday(SS_D) AS Weekday,

        Week(SS_D) & '-' & Year(SS_D) AS WeekYear,

        day(SS_D) AS Day,

        date(SS_D, 'MM/DD') AS DateMMDD,

       

        if(Month(SS_D)=4 or Month(SS_D)=5 or Month(SS_D)=6,1,

        if(Month(SS_D)=7 or Month(SS_D)=8 or Month(SS_D)=9,2,

        if(Month(SS_D)=10 or Month(SS_D)=11 or Month(SS_D)=12,3,

        if(Month(SS_D)=1 or Month(SS_D)=2 or Month(SS_D)=3,4)))) AS Quarter,

       

        IF(MONTH(SS_D)> 4 or MONTH(SS_D)= 4, YEAR(SS_D)+1, YEAR(SS_D)) as SalesFY,

          if(Month(SS_D)=1,10, if(Month(SS_D)=2,11,if(Month(SS_D)=3,12,if(Month(SS_D)=4,1,

         if(Month(SS_D)=5,2,if(Month(SS_D)=6,3,if(Month(SS_D)=7,4,if(Month(SS_D)=8,5,

           if(Month(SS_D)=9,6,if(Month(SS_D)=10,7,if(Month(SS_D)=11,8,if(Month(SS_D)=12,9))))))))))))as SalesMonthOrder

       

      Resident

        SS_Cal;

       

       

       

       

      OrdersCalTemp:

      Load Min(BILLDATE) as MinOrdDate,

      Max(BILLDATE) as MaxOrdDate

      Resident BILLMASTER;

       

       

      let EndDate=Date(Peek('MaxOrdDate',0,'OrdersCalTemp'),'$(DateFormat)');

      let StartDate=Date(Peek('MinOrdDate',0,'OrdersCalTemp'),'$(DateFormat)');

      let caldays=(EndDate-StartDate)+1;

      let vToday=num(EndDate);

       

       

      Cal:

      Load RecNo() as RecordNo,

      if(RecNo()=1,Date('$(StartDate)'),Date(peek("D")+1)) as D

      autogenerate(caldays);

       

       

      Concatenate(SS_Calendar)

      Calendar:

      LOAD

        D AS BILLDATE,

        Floor(D) as NumDate,

        D AS Date,

        Year(D) AS Year,

        Month(D) AS Month,

        week(D) AS Week,

        weekday(D) AS Weekday,

        Week(D) & '-' & Year(D) AS WeekYear,

        day(D) AS Day,

        date(D, 'MM/DD') AS DateMMDD,

       

        if(Month(D)=4 or Month(D)=5 or Month(D)=6,1,

        if(Month(D)=7 or Month(D)=8 or Month(D)=9,2,

        if(Month(D)=10 or Month(D)=11 or Month(D)=12,3,

        if(Month(D)=1 or Month(D)=2 or Month(D)=3,4)))) AS Quarter,

       

        IF(MONTH(D)> 4 or MONTH(D)= 4, YEAR(D)+1, YEAR(D)) as SalesFY,

          if(Month(D)=1,10, if(Month(D)=2,11,if(Month(D)=3,12,if(Month(D)=4,1,

         if(Month(D)=5,2,if(Month(D)=6,3,if(Month(D)=7,4,if(Month(D)=8,5,

           if(Month(D)=9,6,if(Month(D)=10,7,if(Month(D)=11,8,if(Month(D)=12,9))))))))))))as SalesMonthOrder

       

      Resident

        Cal;

       

       

       

       

       

       

       

      this what i done for common calendar table .

       

      for individual dates no problem, the problem is with when i click on filters like sales financial year it's showing second table dates only not getting the first table dates.