2 Replies Latest reply on Dec 6, 2017 3:03 PM by Zohar Gilboa

    Two date fields on one axis

    Zohar Gilboa

      I have a data source for contacts with participants. Each participant has one enrollment date and could have multiple contact dates. The data is organized as follows:

       

      Participant IDEnrollment DateContact Date
      11/1/2017null
      25/5/20175/5/2017
      25/5/20175/30/2017
      25/5/20177/1/2017
      38/15/20179/15/2017

       

      I need to create a viz that shows total enrollments + total contacts, by month. Meaning, it should count enrollments on their own months and contacts on their own months. So based on this data, I'm looking to see:

      Month

      Total Interactions

      2017-1

      1 (from ID 1’s enrollment)

      2017-5

      3 (ID 2’s one enrollment + two contacts)

      2017-7

      1 (ID 2’s contact)

      2017-8

      1 (ID 3’s enrollment)

      2017-9

      1 (ID 3’s contact)

      Practically I also have an "enrollment ID" and an "Contact ID" that I'm using to count these.

       

      Obviously it would be great to restructure the data to just have one row per interaction type, but unfortunately that's not an option. Any ideas? I tried to create a self-blend with the same data source, creating a relationship between Enrollment Date and Contact Date, but then I started getting "unsupported aggregation" errors for the secondary data source.