7 Replies Latest reply on Oct 19, 2016 8:20 AM by David Li

    Last 12 months filter using "Makedate" function is not working in "Hana" live Connection

    Ganesh Vijay Kumar

      Hello Experts,

       

      I am trying to create a calculate field for last 12 months’ filter.

       

      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.

       

      CO_YEAR_ID: 2013; 2014; 2015; 2016

      CO_MONTH_OF_YEAR_ID: 1,2,3,4,5,6,7,8,9,10,11

       

      I created below mentioned formulas on extracted data to filter last 12 months.

       

      Formula 1: Fiscal Year Date

      DATE(DATEADD('month', -3, makedate([CO_YEAR_ID],[CO_MONTH_OF_YEAR_ID],1) ))

       

      Formula 2: Max Fiscal Year Date less 12M

      DATE(DATEADD('month', -12, {Fixed: Max([Fiscal Year Date])} ))

       

      Formula 3: L12M Fiscal Year Filter

      [Fiscal Year Date] > [Max Fiscal Year Date less 12M]

       

      The above mentioned formula is working fine on extracted data source.

       

      But now I am using "Hana" live connection as datasource, with few blogs realised that "Makedate" function is not available for "Hana" live connection.

       

      Need help on modifying the formula. Is there any alternative way to write above mentioned formula which suits live connection. Please advise.

       

      Quick help is greatly appreciated.

       

      Thanks,

      Ganesh