0 Replies Latest reply: Feb 11, 2013 7:50 AM by 990382 RSS

    Function for expressing LYTD?


      I am looking how I can insert a function into a revenue column to retrieve revenue figures from last year, but specifcally year to date so last year to date.

      I know if I dont want to "hard code" a certain year I use the function year(current_date) and if I want last years figures I use the function year(current_date)-1.
      However, how can I do this for monthly basis? Meaning what is the function if I want to show last year to date (LYTD)? The year(current_date)-1 returns the full year data, however what if I want to show only last year up to February because we are only up to February for this year?
      I know I can use the timestampadd function to retrieve an x period of time in the past, however it still needs to be updated with either -1, -2, -7 etc whatever I want to show. Which means that every month I would still need to go into the column and adjust the function to incorporate (or in this case reduce the digit by 1) another month i.e. -12 then -11 then -10 months from current date etc
      I am looking for a function that would do that automatically thats why I dont think I can use a timestampadd.

      Would appreciate any input or if anybody has this worked out!

      Thank you.