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

# Trying to track duplicate values over time

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+

Thanks,

Ryan

Message was edited by: Ryan Vermilio

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

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

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

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

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))