7 Replies Latest reply: Jan 29, 2017 6:37 AM by Abu Sayeed Baseer RSS

    Calendar Issue

    Abu Sayeed Baseer

      Hello Everyone,

       

      please find the attached Img of visualization currently i am working on. In this One i am facing small issue. When i am trying to take Year (fig1.1.year) as Dimension. All Months Are not displayesd AUG and SEP is missing. i tried to resize the length of the graph still there is no use. And, In the weeks ((fig1.2.weeks). Is there any possibility to display weeks as "Week-1.....Week-53" but not as the numbers as shown 2-2016,3-2016,........

       

      CALENDAR SCRIPT:-

       

      MinMax:

      LOAD

        Min(SL_DATE) as MinDate,

        Max(SL_DATE) as MaxDate

      RESIDENT SALES_HDR;

       

       

      // //Set script variables with min and max order dates

      LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

      LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

      LET vToday = $(vMaxDate);

       

      //Generates a single table with one field containing

      //all existing dates between MinDate and MaxDate.

       

      TempCal:

      LOAD

        Date($(vMinDate) + RowNo() - 1) as TempDate

      AutoGenerate

        $(vMaxDate) - $(vMinDate) + 1;

       

         

      //Drop MinMax table - best practice to drop tables as you go to free up memory

      DROP TABLE MinMax;

       

       

      //Create the Master Calendar

      MasterCalendar:

       

       

      LOAD

        *,

        Week&'-'&If(Week=1 AND Month=12,Year+1, If(Match(Week,52,53) AND Month=1, Year-1, Year)) as WeekYear;  

      LOAD

        Floor(TempDate) as SL_DATE,

        Week(TempDate) as Week,

        Year(TempDate) as Year,

        Month(TempDate) as Month,

        Day(TempDate) as Day,

        WeekDay(TempDate) as WeekDay,

        'Q' & Ceil(Month(TempDate) / 3) as Quarter,

          Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear,

        InYearToDate(TempDate, $(vToday), 0) * -1 as CurYTDFlag,

        InYearToDate(TempDate, $(vToday), -1) * -1 as LastYTDFlag,

         

          If(DayNumberOfYear(TempDate) <= DayNumberOfYear($(vToday)), 1, 0 ) as IsInYTD,

        If(DayNumberOfQuarter(TempDate) <= DayNumberOfQuarter($(vToday)), 1, 0) as IsInQTD,

          If(Day(TempDate) <= Day($(vToday)), 1, 0) as IsInMTD,

          If(Month(TempDate) = Month($(vToday)), 1, 0) as IsCurrentMonth,

          If(Month(AddMonths(TempDate,1)) = Month($(vToday)), 1, 0) as IsLastMonth

       

      RESIDENT TempCal

      ORDER BY TempDate ASC;

       

       

      DROP FIELDS Week, MonthYear; //Created as example for UI dimension

       

       

      //Drop the TempCal table as it is no longer reuired

      DROP TABLE TempCal;