5 Replies Latest reply on Dec 22, 2016 12:41 PM by Gerardo Varela

    Lookup Functions and Subtotals

    derek.dupont

      Hi all,

       

      I am working with the LOOKUP function for the first time and am not sure if I'm hitting Tableau's limit on data prep options.

       

      My data set (example attached as a CSV and within the packaged workbook) is an un-ordered list of "Case" records. There is a one-to-many relationship from "Assets" to "Cases", and a one-to-many relationship from "Accounts" to "Assets". Lastly, each "Case" has a "Created Date" value.

       

      In Tableau, I have placed the "Account", "Asset", and "Case" pills in the Rows section to get a simple table. I then created a calculated field "_Time Between Dates" that utilizes the LOOKUP function to find the difference between the current record's "Created Date" value and the previous record's "Created Date" value:

      This gets me halfway to my goal...

      ...but the remaining half is to then provide subtotal values for each "Asset", the average "_Time Between Dates". However, when I try to use the total options, they are disabled for this calculated field.

       

      I haven't yet read through all the options provided in the thread Why Your Grand Total or Subtotal Isn't Working as Expected , but my gut feeling is that the Tableau fix for this may be complicated or not exist. I figured I would reach out in case anyone else has tried this before. As my actual data source is Salesforce, I am already aware of some good data prep options to explore next (Extracting Salesforce.com (SFDC) data to Tableau | Insights Through Data , Eating Our Own Dog Food: Fast Analytics for SalesForce.com | Tableau Software ).

       

      Thanks,

      Derek

        • 1. Re: Lookup Functions and Subtotals
          Norbert Maijoor

          Hi Derek,

           

          Did  you already encounter this thread on your journey?

          1 of 1 people found this helpful
          • 2. Re: Lookup Functions and Subtotals
            derek.dupont

            Hi Norbert,

             

            Thanks for the response, I hadn't seen that thread before. I really like the data blending approach used to solve that problem (it feels closer to what I would be doing if I was writing a SQL query to get this data, joining a table to itself), but unfortunately I don't think I can translate it to this problem:

            • Ideally, the SampleData - SampleData (copy) relationship would be defined as Account --> Account, Asset --> Asset, and then Case -- > Previous Case, where Previous Case is a new field that identifies the case that chronologically occurred prior to the current case. With this setup, I'd be able to continue on and return a subtotal like done in that thread.
            • The issue is that I don't have a Previous Case field...I could create a calculated field in the primary data source that utilizes the LOOKUP function to get it (substituting LOOKUP(ATTR([Case])) for the DATEDIFF() step), but this gets classified as a Measure, rather than a Dimension, so I cannot use it as a join condition. In the other thread, the new field can be added as a dimension because it is always one year prior to the current field - unfortunately there is no set relationship between cases that I can use here.

             

            Thanks,

            Derek

            • 3. Re: Lookup Functions and Subtotals
              Bridgette Burlingame

              Hi Derek,

               

              You can add a WINDOW_AVG() on top of your LOOKUP() calculation to get the average difference for each asset.

               

              In the attached workbook, you will see that I have used the DATEDIFF() function to get the differences between the days - this is just another, slightly simplified way of doing what you were previously doing. I then created another field to find the WINDOW_AVG() on top of this.

               

              In the workbook, you will also see I have included another solution using a FIXED{} LOD function. This will allow your solution to be more flexible. This solution takes the maximum and minimum day for each [Asset], finds the difference between those, and then divides that by the number of times we need to calculation a difference (i.e. the number of days - 1).

               

              Hope this helps!

              -Bridgette

              1 of 1 people found this helpful
              • 4. Re: Lookup Functions and Subtotals
                derek.dupont

                Hi Bridgette,

                 

                This helps a lot! The WINDOW and LOD functions really are powerful - I should be utilizing those more. It's a bummer we can't get the calculated value displayed like a normal subtotal, but I think that's an acceptable cost for not having an intermediary data preparation step here. And for what it's worth, a more prominent view in my final dashboard will likely drop the "Case" column and display this calculated value directly next to Account and Asset, so the visuals will look fine there.

                 

                Best,

                Derek

                • 5. Re: Lookup Functions and Subtotals
                  Gerardo Varela

                  Hi Derek,

                      I took Bridgette's awesome workbook as a starting point and moved the averages to the subtotal. If you have any questions please post back.

                   

                  Regards,

                  Gerardo