2 Replies Latest reply on Nov 16, 2018 7:50 AM by Stephen Groff

# LOD Needed - But How?

I have an [Event No]

I have a [Start Date]

I have a [Finish Date]

I have number of [Days Open]

PAST DUE >= 31 Days Open

CLOSED = NOT NULL([Finish Date])

I have a list of Months (based on the [State Date]):

January

February

March

April

May

June

July

August

September

October

November

December

Question: How many Events were in PAST DUE Status at the end of each month?

COUNTD( IF [Days Open] >= 31 AND [FINISH DATE]<>Current Month We're looking at THEN [Event No]  ELSE NULL END )

My problem:

1.  I have no idea how to write the calculation above

2.  Because the time series is based on Start Date, the calculation above will only account for 'PAST DUE' that were STARTED in that month.  I need the calculation to ignore the start date completely and look at all previous months as well.

I tried solving this all day yesterday with no solution.  EXPERTS:  Please Advise.

• ###### 1. Re: LOD Needed - But How?

The finish dates don't make much sense to me. For example, for event # 3107344 the start date is in August but the finish date is in October. That period spans more than 31 days - would the event have been considered past due in the month of September, even though the "finish date" was not yet reached? If so, what does the finish date represent?

Anyways, ignoring the finish dates, based on your description above an event was past due for a particular month if

1. The event started prior to the end of the month --> [Start Date] <= [End of Month Date]
2. The event was still "open" by the end of the month --> [Start Date] + [Days Open] > [End of Month Date]
3. The number of days between the end of the month and the start date are greater or equal to 31 --> [End of Month Date] - [Start Date] >= 31

That's not the correct Tableau syntax, but it should give you an idea of how to set up the calculated fields.

As for the data, I would recommend creating a new data set with [Month Name] and [End of Month Date], cross join it with your data, then count the number of events meeting the three criteria above.

• ###### 2. Re: LOD Needed - But How?

Thank you for the help.  I'm going to take your suggestion and create a reference table containing the end of month date.

Finished Date = Closed.

I only care about Events that are Past Due (not closed), so that is where the Finish Date comes into play.

I'll let you know how it turns out.  Thanks again!