6 Replies Latest reply: Sep 26, 2011 9:03 PM by Kadir Okan Piroglu RSS

    Last 12 months



      I would like to show last 12 months in my chart dimension. Presently my data is covering dates from 2008 until now and chart is congested. I just learned about flags but unable to locate the correct formula. Is there anybody who could help me? How can I show it at month level instead of day?


      (I am not familiar with set analysis yet, therefore it could be too difficult for me to understand andexecute it at this moment).


      Best regards,


        • Last 12 months
          Dennis Hoogenboom

          Depending on how your date-field looks like you can use something like this in your loading script:


               date(monthstart(YourDateField), 'MMM-YYYY')                     AS MonthYear,




               date(monthstart(YourDateField), 'YYYY-MM')                     AS YearMonth,


          This will create a new field of YearMonth (or MonthYear whatever you prefer) with whiche you can count.

          While you are at it, it might be even bete to create a complete calender
          here is an example of some options:



          YourDateField                                                                                           AS datum,

          week(YourDateField)                                                                                 AS Week,

          year(YourDateField)                                                                                 AS Year,

          month(YourDateField)                                                                       AS Month,

          day(YourDateField)                                                                                 AS Day,

          weekday(YourDateField)                                                                       AS WeekDay,

          'Q' & ceil(month(YourDateField) / 3)                               AS Quarter,

          date(monthstart(YourDateField), 'MMM-YYYY')                     AS MonthYear,

          date(monthstart(YourDateField), 'YYYY-MM')                     AS YearMonth,

          week(YourDateField) & '-' & year(YourDateField)                     AS WeekYear,



          Hope this is what you are looking for.



            • Last 12 months

              Dear Dennis,


              Thanks for your helpful answer. I think I need to build a habit to have and maintain a proper calendar in all projects. I have applied your suggestion and now able to focus on data related fields from different perspectives. (best performing day of week or month etc.)


              For my last 12 months' display query, I thought of the following statement which seems to be working fine.


              Where (created_date)>= AddMonths(Today(),-11);


              However, it applies to all the date fields in my records - which is fine for now but we may not want for other projects. Would you have any better suggestions for it?