6 Replies Latest reply on Nov 7, 2018 6:37 PM by Michel Caissie

    LOD Calculation Help

    Stephen Groff

      Hi All!  My brain is busting over this.  Need some LOD pros... assuming I need an LOD for this.

       

      See attached workbook extract (Tab 5)

       

      I need my data to answer these questions:

       

      Was the event audited?

      Was was the Event #?

      Was there a standard event(s) written for the [Trace No] after it was audited?

      What were those Event #s?

       

       

      Was the event audited?

      The main columns I need to focus on here are [Production ID #] and [Trace No]

           - It's difficult to explain but the [Trace No] is the [Production ID #], but only 1 will populate on a single row of data:

       

      From this I know if the Event was audited:

       

      Easy.

       

      Was there a standard event written for the event after it was audited?

       

      Remember [Trace No] and [Production ID #] are the same number.  They are both Production IDs, but for whatever reason we call one of them TRACE_NUM

       

      In any case, my next calculation needs to go something like this:

       

      IF the event was audited (T/F), and the [Trace No] from that audited event matches the [Production ID #] of a different standard event, then TRUE else FALSE END.

       

      I'm trying to see which audited events still had failures after they were audited.

       

      What were those Event #s?

      I have no idea how I'm going to repopulate different events on the same row of data in my Viz.

       

      Columns I need to show:

       

      Was Event Audited (T/F)?

      Event # of Audited Event?

      Were there Failures After the Audit?

      What were those Event #(s)?

       

      Any help would be greatly appreciated!

        • 1. Re: LOD Calculation Help
          Eric Valpey

          My first thought is that since you are using a custom SQL statement that you could solve for this with a left join to a subquery of distinct Production IDs all audited events.

          • 2. Re: LOD Calculation Help
            Stephen Groff

            That sounds wonderful, except I barely know SQL... putting together the SQL query I have took me an entire afternoon, and it's pretty basic.

             

            I just want to answer a few simple questions... beginning with... Does the Trace number match the production ID # on any row?  If it does then give me that event number(s), else null end

             

            I wish I knew more about fixed LODs

             

            I keep going to {fixed Production ID #:??? (IF production ID # = Trace No THEN 1 else 0 END)} but all that does is return zeros.

            • 3. Re: LOD Calculation Help
              Eric Valpey

              Ah, well the LOD might be:

               

              {fixed [Production ID #]: max(IF [Production ID #] = [Trace No] THEN 1 else 0 END)}

               

              You'd be using max as an aggregate over all cases of that Production ID (so, is this production ID ever equal to the Trace No?)

              • 4. Re: LOD Calculation Help
                Stephen Groff

                Here is a table representation:

                I have 2 different rows.

                -If the Production ID only shows up - its a failure  (This row of data was only created because we systematically record failure events)

                -if the Trace No shows up - it means it was audited  (This row of data was only created because we systematically created an event to show that we audited the event)

                 

                So pretty much I can create 2 different calcs:

                1.  was this a failure event (IF not ISNULL (Production ID) then "Failure" END)

                2.  was this an audit event (IF not ISNULL (Trace No) then "Audited" END)

                 

                I just don't know how to put both of these on the same row.

                IF Event was "Audited" then give me the Event # for the "Failure"

                 

                I can create and join two different tables, but then I just have a list of duplicated events which takes the functionality away from the "VIEW" data feature that makes (in our facility's case) worth using.

                 

                I think this can be done using LODs, but even though I've spent hours trying to understand them... I'm still waiting for the light bulb to turn on.

                • 5. Re: LOD Calculation Help
                  Eric Valpey

                  Seems like you want to LEFT JOIN your datasource to a duplicate of itself where you join [Trace No] = [Production ID No] and filter out all NULL values in the second datasource (and maybe in the first as well).

                  1 of 1 people found this helpful
                  • 6. Re: LOD Calculation Help
                    Michel Caissie

                    Stephen,

                     

                    You can still manage to do this with Tableau.  What you need first is a 'GroupBy' dimension with the same value for all rows having either the same productionId  or Trace No.

                    You can do this by concatenating both dimension and trim the spaces , like this

                    TRIM( IFNULL( STR( [Production ID #] ),'' ) + IFNULL( STR( [Trace No] ),'' ) )

                     

                    With this you can now do lods to get the numbers.  I am a bit confused on what you want to compute exactly, the number of groupBy,  or the number of event etc,...

                    But anyway, you will find a couple of boolean on Sheet6  that you will be able to use to build your logic.

                    For example;

                     

                    GroupHaveAtLeastOneAudited

                    {FIXED [GroupBy]: MAX( if [Was Event Audited?] then 1 else 0 end )} = 1

                     

                    or

                     

                    GroupHaveNoProductionId

                    {FIXED [GroupBy]: MAX( if not ISNULL( [Production ID #] ) then 1 else 0 end )} = 0

                     

                    One last thing, since these are  FIXED lods,  if you plan to filter on dates, make sure to add the filter to context,  in order for the lod to compute after the  filtering.

                     

                    Michel