2 Replies Latest reply on Jan 14, 2020 12:14 PM by Jim Dehner

# LOD Calc?

Attached is an Excel file with sample data.

Here's a snapshot of the sample data.

I need to calculate the amount of time between various Status values at the ID level. A Status value can occur more than one time (i.e., more than one Status Date) for a Status, and when it occurs more than one time, then use the later Status Date. Sometimes the Status value of interest does not exist at the ID level.

For example, for ID = AAA, I need to know the amount of time between the latest Apple Status, and the latest Banana Status. I also need to know the amount of time between the Dates Status and the Eggs Status. I'll then be using these values to find average time between the Apple and Banana Status across all of the IDs, and the average time between the Dates and Eggs Status across all of the IDs.

I've tried all kinds of LOD calc's but can't seem to get it right.

Can someone help? (I'm using Tableau v2018.2.)

Sue

• ###### 1. Re: LOD Calc?

Good morning Sue

Is this a restatement of a previus question - That's OK just wondered - I am out on the road till later today and cant do a complete solution for you now

LOD's are not the way to go

you are looking at different row level data in a table calculation so you will need to use table calcs

firs use Last() to find the last value in each status -

then you will need to calculate the difference between the "Last values" using lookup    on the current row and the previus

and finally a window average on the differenc

you will need to restart the calculation on the Item (for the average) and teh status for last

sorry I can't get to till later

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: LOD Calc?

see the attached

I gave this a shot - once I got into it I found out this was more complex than first appeared

see if this makes sense - it is combination of an LOD to get the Last time for each ID/status combination then table calcs

it returns this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.