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,........






        Min(SL_DATE) as MinDate,

        Max(SL_DATE) as MaxDate




      // //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.




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


        $(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






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


        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;