1 Reply Latest reply: Jan 28, 2015 9:41 PM by jagan mohan rao appala RSS

    15 months graph

    Sujan Janardhan

      I have a requirement of showing 15 months of for CY and LY.Currnetly i have a graph which does for 12 months as shown below.My master calendar has a cannonical date for Hire date and Term date and the fical month is being derived using this date.

       

      My master calendar is :

      SET vFM = 10;

      SET vFiscalYearStartMonth = 10;

      LET vStartDate = Num(YearStart(Today(), -1));

      LET vEndDate = Num(YearEnd(Today()));

       

      FiscalCalendar:

      LOAD

      *,

      Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

      Dual(Text(Date(MonthEnd(Commondate), 'MMM')), FiscalMonth) AS FiscalMonthName, // Fiscal Calendar Month Name

      Dual(Text(Date(MonthEnd(Commondate), 'MMM')), FiscalMonth)&'-'& YearName(Commondate, 0, $(vFiscalYearStartMonth)) AS FiscalMonthName_Year; // Fiscal Calendar Month Name

      LOAD

      *,

      Mod(Month(Commondate) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal Calendar Month

      YearName(Commondate, 0, $(vFiscalYearStartMonth)) AS FiscalYear,  // Fiscal Calendar Year

      If(QuarterStart(Commondate,0,$(vFM))=QuarterStart(Today(),0,$(vFM)),1,0) as IsCurrentQuarter,

      If(YearStart(Commondate,0,$(vFM))=YearStart(Today(),0,$(vFM)),1,0) as IsCurrentYear,

      If(YearStart(Commondate,0,$(vFM))=Date(YearStart(Today(),0,$(vFM))-365),1,0) as IsLastYear;

       

       

      LOAD

      Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Commondate,

      RangeSum(Peek('RowNum'), 1) AS RowNum

      AutoGenerate vEndDate - vStartDate + 1;

       

      and my two measures are  :

      All Hires CY: Count({<Year=, Month=, Quarter=, Week=, DateField=, HireDateNum={">=$(=Num(YearStart(Today(),0,10)))<$(=Num(Today()))"}

      ,DataType = {'Hire'}>} distinct [EmpUID])

       

      All Hires LY :

      Count({<Year=, Month=, Quarter=, Week=, DateField=, HireDateNum={">=$(=Num(YearStart(Today()-365,0,10)))<$(=Num(Yearend(Today()-365,0,10)))"}

      ,DataType={'Hire'}>} distinct [EmpUID]).

       

      Can you please help me on how to generate a grpah with 15 months showing for CY and LY.

       

       

       

        • Re: 15 months graph
          jagan mohan rao appala

          Hi,

           

          Try this for last 15 Months based on the Current Date

           

          =Count({<Year=, Month=, Quarter=, Week=, DateField=, HireDateNum={">=$(=Num(MonthStart(Today(), -15)))<$(=Num(Today()))"}, DataType = {'Hire'}>} distinct [EmpUID])

           

          Based on the selected date

          =Count({<Year=, Month=, Quarter=, Week=, DateField=, HireDateNum={">=$(=Num(MonthStart(Max(DateField), -15)))<$(=Num(Max(DateField)))"}, DataType = {'Hire'}>} distinct [EmpUID])

           

          Hope this helps you.

           

          Regards,

          Jagan.