2 Replies Latest reply on Jun 12, 2015 2:47 PM by Tom Rowe

    BI Answers Analysis 'yyyymm' Column Filtering Expression For Previous Month ?

    Tom Rowe

      Hi,

       

      I have column which is in 'yyyymm' which I need to filter for the previous month.

       

      Initially I came up with the following filter SQL expression...

       

      "Test_Table"."Month" = REPLACE(CAST(TIMESTAMPADD (SQL_TSI_MONTH, -1, CURRENT_DATE) AS VARCHAR (7)),'/','')

       

      While the expression produces 201505 for last month when used as a filter no results are returned. So I came up with the following...

       

      "Test_Table"."Month" = YEAR(TIMESTAMPADD (SQL_TSI_MONTH, -1, CURRENT_DATE)) * 100 + MONTH(TIMESTAMPADD (SQL_TSI_MONTH, -1, CURRENT_DATE))

       

      The second filter expression worked and the desired results were returned. However I don't understand why the first filter expression returned no results and also if there is a better solution to do this type of filtering as what I eventually came up with to get it working seems to be a bit messy and perhaps there is a more elegant solution ?

       

      Regards,

       

      Tom Rowe