1 2 Previous Next 28 Replies Latest reply on Sep 7, 2016 7:28 AM by Ganesh Vijay Kumar

    Last 6 Months Filters using Fiscal Year and Fiscal Month Numbers.

    Ganesh Vijay Kumar

      Hello Experts,

       

      I am trying to create a calculate field for last 6 months filter. I am partially successful in creating one, but not completely satisfied with the results as there will be problem in future.

       

      Our fiscal calendar starts from October and ends on September e.g. FY 2016 = Oct 2015 to Sep 2016

       

      Below are the fiscal year and fiscal month columns i have in the database ( I have data only for FY 2016 i.e. From OCT 2015 to Aug 2016 (11 months)).

       

      CO_CALENDAR_YEAR_ID ( string)      : 2015 ; 2016

      CO_CALENDAR_MONTH_NM ( String) : JANUARY ; FEBRUARY; MARCH.......DECEMBER

      CO_YEAR_ID(INT) : 2016

      CO_MONTH_NUMBER_NM (INT): 1,2,3,4,5,6,7,8,9,10,11

       

      To achieve last 6 months filter i created few calculated fields mentioned below.

      Calc 1 : Month of Analysis :

      MAKEDATE(

      INT([CO_CALENDAR_YEAR_ID]),

      CASE UPPER(LEFT([CO_CALENDAR_MONTH_NM],3))

      WHEN 'APR' THEN 4

      WHEN 'AUG' THEN 8

      WHEN 'DEC' THEN 12

      WHEN 'FEB' THEN 2

      WHEN 'JAN' THEN 1

      WHEN 'JUL' THEN 7

      WHEN 'JUN' THEN 6

      WHEN 'MAR' THEN 3

      WHEN 'MAY' THEN 5

      WHEN 'NOV' THEN 11

      WHEN 'OCT' THEN 10

      WHEN 'SEP' THEN 9

      END,

      1

      )

      Calc 2 : Data Update Till :

      {MAX([Month of Analysis])}

       

      Calc3 : L6M Filter

      [Month of Analysis] > DATEADD('month',-6,[Data Update Till])

       

      This really worked for me! below is the screen shot, we are in 11(Aug) month as per fiscal calendar and the 6 months filter is working absolutely fine.

       

      As I have used "Max" function not sure if it works once we move to Fiscal year 2017 that is Oct-2016 to Sep - 2017.

       

      And also I have to show Oct 2015 calendar year as Oct 2016 as to match our fiscal calendar. hence I tried using the below mentioned formula:

      MAKEDATE(

      CASE UPPER(LEFT([CO_CALENDAR_MONTH_NM],3))

      WHEN 'NOV' THEN INT([CO_CALENDAR_YEAR_ID])+1

      WHEN 'OCT' THEN INT([CO_CALENDAR_YEAR_ID])+1

      WHEN 'DEC' THEN INT([CO_CALENDAR_YEAR_ID])+1

      ELSE INT([CO_CALENDAR_YEAR_ID])

      END,

      CASE UPPER(LEFT([CO_CALENDAR_MONTH_NM],3))

      WHEN 'APR' THEN 4

      WHEN 'AUG' THEN 8

      WHEN 'DEC' THEN 12

      WHEN 'FEB' THEN 2

      WHEN 'JAN' THEN 1

      WHEN 'JUL' THEN 7

      WHEN 'JUN' THEN 6

      WHEN 'MAR' THEN 3

      WHEN 'MAY' THEN 5

      WHEN 'NOV' THEN 11

      WHEN 'OCT' THEN 10

      WHEN 'SEP' THEN 9

      END,

      1

      )

       

      The moment I tried to change OCT 2015 to 2016, the Max function do not work as Dec-2016 is greater than Aug-2016 which is my current month.

       

      Now I am clueless how to go about it. Need your help to achieve the desired results.

       

      Could we use the below mentioned columns which is always as per fiscal calendar and create a calculated field for "last 6 months filters".

      CO_YEAR_ID(INT) : 2016

      CO_MONTH_NUMBER_NM (INT): 1,2,3,4,5,6,7,8,9,10,11

       

      I believe we need to consider two variables "CO_year" and "CO_MONTH_NUMBER_NM".

       

      If we move to fiscal year 2017 my last 6 months filter should be

       

      May 2016

      June 2016

      July 2016

      August 2016

      September 2016

      October 2017

       

      I am struggling to find a solution, any help in this regard is greatly appreciated.

       

      Thanks,

      Ganesh

        1 2 Previous Next