3 Replies Latest reply on Jul 14, 2016 8:35 AM by swaroop.gantela

    Trying to track duplicate values over time

    Ryan Vermilio

      Hello all,

       

      This is my first post to the forum.  I'm stuck on a problem and hope someone can help.

       

      I have a unique identifier (let's call it account #) as my row and dates as my columns.  I want to create a side by side bar chart (I think).  The first bar will represent the Total number of unique accounts appearing in that day.  In the second bar or bars, I want to show the count of the number of account recurrences over the next 8 day period for the same cohort.  Ideally, these would be grouped as follows:

       

      Recurrence Groups (Total of 6):

       

      1, 2, 3, 4-6, 7-9, 10+

       

      Any help you can offer would be greatly appreciated.

       

      Thanks,

      Ryan

       

      Message was edited by: Ryan Vermilio

        • 1. Re: Trying to track duplicate values over time
          swaroop.gantela

          Ryan,

           

          Welcome to the Forums.

          Please see if the attached could be a starting point for you.

           

          Firstly, I joined the dataset to itself to get all the combinations of dates per acct#

          so that I could calculate the 8 day difference.

           

          So per each Acct, each [Date] and list of all the other possible [Date (data$1)] .

           

          I pulled out accts on the day of:

          IF [Date]=[Date (data$1)]

          THEN [Acct#]

          END

           

          Then I pulled out the accts that met the 8 day criterion:

          IF DATEDIFF('day',[Date],[Date (data$1)])<=8

          AND [Date]<[Date (data$1)]

          THEN [Acct#] END

           

          I then used Level of Detail calculations to get the counts of these.

          For Day of:

          { FIXED [Date],[Date (data$1)]:COUNT([AcctDayOf])}

          Similiarly for Flag8:

          { FIXED [Date],[Date (data$1)]:COUNT([Flag8])}

           

          Then the grouping was done with:

          IF SUM([CountFlag])<1 THEN "<=1"

          ELSEIF SUM([CountFlag])=2 THEN "2"

          ELSEIF SUM([CountFlag])=3 THEN "3"

          ELSEIF SUM([CountFlag])>=4 AND SUM([CountFlag])<=6 THEN "4-6"

          ELSEIF SUM([CountFlag])>=7 AND SUM([CountFlag])<=9 THEN "7-9"

          ELSE "10+"

          END

          210942count.png

          2 of 2 people found this helpful
          • 2. Re: Trying to track duplicate values over time
            Ryan Vermilio

            Hello Swaroop,

             

            First of all, thank you so much for this solution.  Everything is working great up to the point where I go to create the level of detail expressions.  I'm using an MS Access data source and apparently, LOD expressions aren't supported when using an MS Access data source.  Is there a workaround for this, or if not, what data sources are supported?

             

            Thanks again,

            Ryan

            • 3. Re: Trying to track duplicate values over time
              swaroop.gantela

              Ryan,

               

              I think you will also be able to achieve it with Window Calculations.

              For counts dayOf I used:

              WINDOW_SUM(SUM(IF NOT(ISNULL([AcctDayOf])) THEN 1 ELSE 0 END))

              Similarly for FlagCount:

              WINDOW_SUM(SUM(IF NOT(ISNULL([Flag8])) THEN 1 ELSE 0 END))

               

              210942count2.png