9 Replies Latest reply on Jan 18, 2019 11:39 AM by Joe Oppelt

    Conditional aggregation of a measure

    Christoph Klavehn

      Dear Community,

      Hopefully I am not missing the obvious as I am new to Tableau: I am looking for a way of aggregating a measure based on the category it describes.

       

      My data has the following structure:

      The measure is called "value" and is a number on a scale between 1 and 10 points given, for a number of countries and years. Each value of measure is associated with a factor, indicator and dimension (not in the tableau sense of 'dimension'). Between the latter three is a hierarchy of factor < indicator < dimension.

       

      Having the years in columns and the measure in rows I'd like to use filters to switch between various combinations of countries, factors, indicators, and dimensions. This is straightforward if all factors and indicators would follow the same aggregation rule (e.g. average). I am attaching a workbook to illustrate this setup.

       

      However, I would like to adjust the aggregation rule for an indicator's set of factors. For example, in the workbook, "approval" is the simple average of six different factors (correctly shown). By contrast, "funding" is the sum of two its factos (currently shown as their average).

       

      I am wondering whether/how this can be done, i.e. aggregating the value depending on the selection in the filters and a specific aggregation rule for each (in the way in now works for "approval" in the example workbook).

       

      Hope this makes sense - many thanks for any ideas!

       

      I am using Tableau Desktop 2018.3.2.


      Chris

        • 1. Re: Conditional aggregation of a measure
          Jim Dehner

          sorry but not real clear on the goal but it sounds like you want to apply weights to the values by group

          you can do that with something like this

          then apply the weights to the values

           

          you would adjust the weights and aggregation to meet your needs

          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.

          1 of 1 people found this helpful
          • 2. Re: Conditional aggregation of a measure
            Joe Oppelt

            In the attached, I made SHeet 2 to see the interplay between Indicator and Factor.

             

            I understand (I think) what you want to do.


            On Sheet 1, see my calc, [display value].  It conditionally aggregates [Value] based on what the [Indicator] value is.

            • 3. Re: Conditional aggregation of a measure
              Christoph Klavehn

              Thanks much for this, Joe! Your formula did the trick for the level of indicators. I also added respective conditions for the dimension level and that seems to work too:

              Prompted by Jim's comment, I am wondering how this could be finetuned for each Indicator/factor? For example, "approval" is currently the average of its 5 factors. But how if one wants to weigh one of the five double, or substract one from the other four to arrive at the "approval" score?

              • 4. Re: Conditional aggregation of a measure
                Joe Oppelt

                First let me ask this.  Are combinations of [Dimension] and [Indicator] mutually exclusive?

                 

                For example, what if the [Dimension] is "individual" and the [indicator] is "funding"?

                • 5. Re: Conditional aggregation of a measure
                  Christoph Klavehn

                  Yes, they should be exclusive. [Dimension] can only have its respective [indicator]s.

                  • 6. Re: Conditional aggregation of a measure
                    Joe Oppelt

                    In the attached I made calcs to pick out "csfp" from the Approvals, and to pick out all the rest from Approvals.  I display these on Sheet 2.  Also I made a calc to tell me how many Factors are in each Indicator.

                     

                    I used FIXED LODs for these.  Notice that [CFSP Approval] has designated the level if [Indicator] and [Year].  What this says is to do a SUM of all the [Value] amounts for each combination of [Indicator] and [Year].  And in the SUM() I placed an IF statement to grab only the values in the rows where we have "csfp".

                     

                    Likewise, I do the same for the NOT-csfp calc.  At first I didn't have the extra check in there for Indicator="approval", and I got values for all Indicators.  This may or may not matter depending on one's data and business needs, but I added it in there for  more complete example.  Notice that this one is also at the [Indicator] and [Year] levels.  At first I didn't have Year in there, and on Sheet 1 it gave me a straight line because all the values across all years were being added up, resulting in one constant value.  But when you're on Sheet 2 you'll see that even with the [Year] level in the calcs, we get rolled  up values because I don't have Year on the sheet.  That's just Tableau doing what it does, summing up all the values given the dimensions on the sheet.

                     

                    On Sheet 2(2) I added Year to ROWS.  Now you see the values broken out by year.  Notice that all the LOD values are repeated from Factor to Factor.  If I wanted to, I could add in [Factor] to the LOD lists of levels, and the propagation of the values in the other factors would go away.  AND the results on Sheet 1 would not change.

                     

                    LODs are a really cool feature that they added in version 9.0.

                     

                    So now look at Sheet 1.  I changed [display value] to use these LODs for the Approval part of the calc.  I doubled "csfd" and subtracted it from CSFD-NOT, and divided it by the number of Factors.

                     

                    Long story short:  You can break out pieces and parts, and incorporate them into your display calc.

                     

                    See attached.

                    1 of 1 people found this helpful
                    • 7. Re: Conditional aggregation of a measure
                      Christoph Klavehn

                      Joe, this is truly helpful! Thank you for the detailed explanation and updated workbook which helped me in understanding the usefulness of your approach with LOD.

                       

                      What I would really like to keep is the ability to tick and untick the individual factors in the filter card on sheet 1.

                      To do that I added [Factor] to your LOD expressions:

                       

                      With that I could toggle the factors on and off in the filter card with one caveat: It only works as long as at least [cfsp] and one of the other four is selected. However, if only [cfsp] or either combination of the other factors are selected (and [cfsp] not with them) the graph is empty. I tried to solve this by including another IF condition in your adjusted [display value] for those cases when [CFSP NOT Approval] or [CFPS Approval] are 0 ("display value 2"):

                       

                      I put that to rows on Sheet 1 (2) in the workbook "forum C" (linked above) but there is no effect. The graph still goes blank in those selections when [cfsp] is the only selected item or when it is missing.

                       

                      In other words, I would like Tableau to show the conditional aggregation only if the filter selection includes a combination of factors that includes those factors that are weighted against each other. In this example, [cfsp] should only be counted twice when it is selected together with another [approval] factor. If it is selected alone or of the other factors are filtered everything should be treated as average.

                       

                      It feels like the solution is close. Many thanks if you can share any further advice!

                      • 8. Re: Conditional aggregation of a measure
                        Joe Oppelt

                        Christoph Klavehn wrote:

                         

                         

                        To do that I added [Factor] to your LOD expressions:

                         

                        ...

                         

                        With that I could toggle the factors on and off in the filter card with one caveat: It only works as long as at least [cfsp] and one of the other four is selected. However, if only [cfsp] or either combination of the other factors are selected (and [cfsp] not with them) the graph is empty. I tried to solve this by including another IF condition in your adjusted [display value] for those cases when [CFSP NOT Approval] or [CFPS Approval] are 0 ("display value 2"):

                         

                         

                        Adding [Factor] to the LOD list granularizes the data aggregation at the [factor] level.  So that means you'll only get 2513 as a value for CSFP approval on an actual CSFP row.  Take a look at my two calcs in the attached.  I moved [Factor] to its own line in each calc, and right now its commented out.  So it's working back the way I originally had it.  And in Sheet 2 in the attached I have [Factor] as a filter, and three values selected.

                         

                        By not evaluating at the [Factor] level, I get the "csfp" value on all rows.  In fact, uncheck cfsp in the filter, and you'll see that the value STILL appears.  That's because a FIXED LOD evaluates before filters are evaluated.  (It's called "order of operation".)  And we see 9563 for NOT CSFD on all rows, even though not all the "approval" factors are selected.

                         

                        So the filter is controlling what factors will display on the sheet, but that LOD is plowing through all of the data in spite of the selections in the filter.  (When I created that was the expected result I was aiming for.)

                         

                        If you want the filter to control the FIXED LOD, you can right click on [Factor] in the filters shelf and select "Add to context".  Context filters evaluate as the data is getting pulled onto the sheet, and BEFORE even a FIXED LOD gets evaluated.

                         

                        (Special note:  There are also INCLUDE and EXCLUDE LODs, and they always get evaluated after filters, whether the filters are in context or not.)

                         

                        When you changed the level of detail in the FIXED LODs to evaluate at the [Factor] level, the value of the calc is not propagated to all factors.  Maybe that's what you want.  Maybe not.  There are a lot of moving parts here, and you just need to decide what you need to see.  If you want the CSFP value to display whether or not CSFP is actually selected, you need to keep [Factor] off the LOD list.

                        • 9. Re: Conditional aggregation of a measure
                          Joe Oppelt

                          Just to add one more thing to what I said...

                           

                          Take sheet2.  (PS:  I forgot to upload it to the last reply, so it's attached to this one.)  Select ALL for Factor (so all Indicators show up.)  Edit [CSFP Approval] to take out [Indicator].  You'll see that the CSFP value propagates all the way down the sheet.  It's still just the CSFP value, but it now gets put on every row.  But now, if "csfp" had happened to appear under any other [Indicator], the sum of all the "csfp" rows across all [Indicator]s would have gotten added up.