4 Replies Latest reply on Nov 19, 2018 9:49 AM by Don Wise

# Line graph with unique contracts active between start and end date

Hi everyone,

I have an issue that I need help with: I have some data containing client ID, start dates and end date for which I'm trying to create a graph of total number of contracts that are active at any given time, but only for unique clients.

I have tried numerous things but could only manage to present a line graph with the amount of contracts that are open, regardless of unique client ID (using running sum over time). How do I do the same, but only for those that are unique? So for example, on 2-6-2013, there should be 1 contract open instead of 2.

I have attached the sample excel sheet.

Really appreciate any help!

Thanks,

Sander

• ###### 1. Re: Line graph with unique contracts active between start and end date

Hello,

I usually take the following approach. Add in a Calendar Date (added in MS-Excel worksheet) for each possible date in the view, which provides a continuous date whether in your data or not.  Technique is called date scaffolding.

Then a simple filter calculation of the following to filter the view into a line chart representing the actual number of open contracts.  Please see attached 2018.3 workbook and below screenshot; hope it helps! Thx, Don

[Calendar Date]>=[Open Date]

AND

[Calendar Date]<=[Close Date]

1 of 1 people found this helpful
• ###### 2. Re: Line graph with unique contracts active between start and end date

Thanks but how did you combine the two files? I see you did an inner join but I can't seem to replicate it.

• ###### 3. Re: Line graph with unique contracts active between start and end date

Can you explain me how you did date scaffolding? I haven't found a tutorial that showed how to do this properly.

• ###### 4. Re: Line graph with unique contracts active between start and end date

Hello,

Please see below screenshot. This separate worksheet then provides a base of continuous dates as an independent bridge between your Open and Closed dates. The calculation looks forward and backwards between your open/closed dates and the base calendar dates to see whether any of your contracts meet the criteria; if yes, then mark as True. Drag the calculation to the filters card and set to true.