2 Replies Latest reply on Jul 22, 2013 6:45 AM by John Sobczak

    How Do I Plot Activity Against Dates for Generic Schedules? - Date Modeling

    John Sobczak

      I have a generic report schedule that lists reports which are created daily, weekly, monthly, quarterly or annually.  The weekly reports are defined by what day of the week they are created, and the monthly, quarterly and annually are defined by what business day of the month they are created.  There are no dates in this table as it is a generic schedule.

       

      How can I plot the # of reports for each day plotted against date axis? I can create a generic date table but there is no date in the original table to join it with.  I have considered blending it (many-to-many) with the day of the week in the report table, but this limits to weekly reports.  Maybe I should reshape and expand Daily, Monthly, Quarterly and Annual records to every day of the week combination and blend on that (ie 5 records for each report)?  But then it seems I need to also blend on day on the month for the monthly and quarterly reports?

       

      Is there a way to generically use now() function recursively by continue to add 1 day as well as subtract one day to establish date axis?

       

      Attached is a mocked up report as well as date table.