3 Replies Latest reply on Jul 7, 2017 8:53 AM by Jim Dehner

    Manipulating single field with stacked dates

    John Conlon



      My data is structured with an 'event_date' column that contains dates and an 'event_date_type' column that contains the date type, such as booked date, offer date, etc.  I am trying to determine the number of active offers by month based on a start date and an end date.  The date range may span multiple months.  If each date type were it's own field (and thus each row contained both start and end date), this formula should work nicely...


      IF [offer_exp_dt]>=[begin_date_month]

      AND [offer_apr_dt]<=[last_date_month]

      THEN 1



      Start date of month and End date of Month


      But since the data is stacked, the above formula doesn't work.  In short, I need to count all of the end dates (offer_exp_dt) that happen after the first of the month and all of the start dates (offer_apr_dt) that happen before the end of the month.  That should give me the total number of active offers by month regardless of whether an offer started or ended mid month.  So how can I do this without restructuring the data?


      Please see attached for sample workbook.  I'm using Tableau 10.