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

    LOD Calc?

    Sue Grist

      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?
          Jim Dehner

          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?
            Jim Dehner

            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.