5 Replies Latest reply on Apr 28, 2017 11:37 AM by Elliott Stam

# Count Occurrences Before and After a Fixed Date

Hi All,

After receiving such great help for my first question, I've been inspired to ask another question I've been trying to tackle.

I've got source data at a line item level that I need to roll up by date (using a level of detail expression I am able to count the distinct occurrences of a date field). What I want to know is how many of these occurrences were BEFORE a different static date field, and how many were AFTER the static date field.

I've included a sample worksheet with data that supports this scenario with a caption at the bottom with the desired solution.

Thanks in advance for all of the great help and support.

Tableau Desktop Version 10.2.1

• ###### 1. Re: Count Occurrences Before and After a Fixed Date

Hi Dan,

The attached workbook has what you're looking for.

The calculation boils down to this LOD calculation:

It fixes the calculation to each person and their date of service, assessing whether or not the service happened before or after the date of action.

This formula outputs the result as a dimension, so you can use it to create the exact view you wanted in the caption.

In this workbook I also added an additional formula to help replace the blank that would exist for "Person 2" with a zero:

Hope that helps!

Elliott Stam - Interworks

• ###### 2. Re: Count Occurrences Before and After a Fixed Date

I think this would service your BEFORE calculation. The AFTER would be the same, but reversing the operator, and in one of them you would probably want to include >= or  <= so they meet but don't overlap.

{ FIXED [Person]: COUNTD(if [Date of Service] < [Date of Action] then [Date of Service] end) }

The IF statement inside the countD is the first to be evaluated, at the row level. So each row asks is Date1 < Date2 and if it is, returns Date1. The CountD counts the distinct values returned by the inner calculation, and the FIXED sets a partition at Person Level, so that no matter how you drop pills, you will have Person details returning, so that, for example, you could take an average of this calculation result.

That said, I think your existing count LOD can be simplified - the Date of Service in the partitioning is redundant.

• ###### 3. Re: Count Occurrences Before and After a Fixed Date

Elliott Stam Is there a reason to put this into a LOD calculation at all if you're just returning Before/After label? Why not just do the if statement all by itself since there is effectively no aggregation happening?

• ###### 4. Re: Count Occurrences Before and After a Fixed Date

Perfect - thanks so much Elliott. Not sure I would have gotten here so very much appreciate the help!

Kind Regards,

-Dan

• ###### 5. Re: Count Occurrences Before and After a Fixed Date

Good point, Justin. You're right, in this case you don't need the LOD calc to get to the desired result. The data here is simple enough that all you need is the if/then comparison between date of service and date of action.