4 Replies Latest reply on Nov 25, 2015 2:50 PM by Shaun Currier

    Getting SUM when I expect AGG, when dragging out LOD calc from measure drawer

    Shaun Currier

      Hello,

       

      What I am trying to do overall

      My company sells widgets that have an hour meter on them to tell how long they've run for.  I want to calculate some statistics about how much they run per year.  I want to look on a per-widget basis, but also on two other levels above the widget level- call those levels the model and the product family.  When I look at the model level, Tableau will naturally give me statistics about the level below the model level, which is the widget level.  That's what I want.  But when I look at the product family level, Tableau will still naturally give me statistics about the widget level, but instead I want the stats aggregated once at the model level before rolling up to the product family level.

       

      The columns I am working with as it relates to this problem

      [Tot Hr Cum Qty] - the hour meter reading for the widget (direct from database source)

      [Hrs Since Delivery] - the number of hours since customer recieved the widget (calc'd from two other columns I won't describe here)

       

      My main calculation

      AVG([Tot Hr Cum Qty]/[Hrs Since Delivery]*365*24)

      This will calculate the number of hours a widget has run per year that the customer has had it.

      To get the right cascading of statistics that I described in "what I am trying to do overall" (above), I am wrapping this in a LOD expression:

      {INCLUDE [model]:AVG([Tot Hr Cum Qty]/[Hrs Since Delivery]*365*24)}

       

      My problem

      When I use the non-LOD version of my main calculation, I can drag the measure pill out of the measure drawer and the pill will show the AGG() function wrapping the measure.  This is proper and expected because the calc contains the AVG() function, which is an aggregation function.  But when I try to use the LOD version of my main calculation, dragging the pill results in the SUM() function wrapping the measure.  I don't want a sum of averages because that makes no sense.  I just want control over how the AGG() aggregation is happening and that's what I thought that the LOD expression would provide me.  But for some reason the LOD expression is preventing me from aggregating at all.

       

      I've tried changing all kinds of things around to see if I could suss out what is causing this issue, but I have not been able to find it.  I'd appreciate any help that anyone can give me.  Unfortunately, I don't think I'll be able to send the worksheet since it relies on a database connection and some proprietary data.

        • 1. Re: Getting SUM when I expect AGG, when dragging out LOD calc from measure drawer
          Robert Rouse

          Shaun

           

          A good way to think of LOD is that it can act similar to a row-level value when you need to aggregate at a different level than the row itself. There are two solutions to controlling the aggregation:

           

          1) Right-click the field, choose Default Properties->aggregation.

          2) wrap the calculation in the desired aggregation, like this:

          AVG(

               {INCLUDE [model]:AVG([Tot Hr Cum Qty]/[Hrs Since Delivery]*365*24)}

          )

           

          Robert Rouse

          InterWorks, Inc.

          • 2. Re: Getting SUM when I expect AGG, when dragging out LOD calc from measure drawer
            Shaun Currier

            Thanks for your response, Robert.  Can you clarify a few things about the two solutions you propose?

             

            1. What is the purpose of specifying another aggregation function via the default properties>aggregation menu?  There is already an AVG() function in the calculation.
            2. What is the outer AVG() function doing that the inner AVG() was not doing?  Isn't the main purpose of LOD expressions to be able to cascade the averages within the LOD expression in a prescribed way without wrapping multiple AVG() functions?

             

            Thanks for your help.

            • 3. Re: Getting SUM when I expect AGG, when dragging out LOD calc from measure drawer
              Robert Rouse

              Let me try to explain with an example. Below we have multiple rows of data for each model, each with a value (the result of the [Tot Hr Cum Qty]/[Hrs Since Delivery]*365*24 calc) that we want to measure:

                 

              ModelValue
              Model 11
              Model 12
              Model 13
              Model 25
              Model 26
              Model 27

               

              AVG([Value]) takes those values and simply averages them. Now, let's say you first need the average for a particular model first, then you want the average of those averages. The LOD calc, {INCLUDE [model]:AVG([Tot Hr Cum Qty]/[Hrs Since Delivery]*365*24)}, results in data that looks something like this:

               

                 

              ModelModel Average
              Model 12
              Model 26

               

              Now, we have two numbers but we still have to tell Tableau how to aggregate those model averages. Do I want to sum up the averages, get an average, a percentile, etc? That's what wrapping the whole thing in AVG() or specifying a default aggregation does for you. In this simplistic example, the results are the same either way but in many cases they won't be.

               

              A really good resource for understanding more deeply what's going on is this page of links: Level of Detail (LOD) Expressions | Drawing with Numbers

               

              Finally, you might consider changing the initial average calculation. I think you want SUM([Tot Hr Cum Qty])/SUM([Hrs Since Delivery]*365*24). Of course, it depends on the data and what you're trying to achieve and without that knowledge I couldn't say for sure how ti affects your analysis.

              2 of 2 people found this helpful
              • 4. Re: Getting SUM when I expect AGG, when dragging out LOD calc from measure drawer
                Shaun Currier

                Robert,

                Thank you so much for your time.  I think you broke the understanding gap for me.

                 

                AVG({INCLUDE [model]:AVG([Tot Hr Cum Qty]/[Hrs Since Delivery]*365*24)})

                 

                If I'm understanding correctly, in the following calculation, the outer AVG() applies at the level of the dimension shown in the visualization, and is taking the place of specifying a default of Average via Default Properties>Aggregation?  And the inner AVG() applies at the level of dimensions specified by the LOD expression (which may not be shown in the visualization)?

                 

                With regards to your final statement suggesting that I may want the aggregation to be a ratio of sums instead of an average of ratios, I did indeed deliberately use an average of ratios, since in my case the ratio of sums has no meaning.  Thanks for pointing out the possibility of an error, though.