5 Replies Latest reply on Feb 17, 2017 10:43 AM by Yingying Zeng

    Confusing values of the measure filter

    Yingying Zeng

       

       

      I am studying the the order of operations and reading the below article with the workbook( the twbx book is attached  as well)

       

      In the sheet 'Solution by Measure + Table Calc Filters' I do not understand why the sum(order of orders) is not the 5009 but the value range from 1 to 17?

       

      Please kindly advise!!! Thanks in advance

       

      *** link and content I was reading

      Vizible Difference: The Order of Operations within Dimension and Set Filters

      4.Conclusion

      By understanding the order of operations of the dimension and set filters, we can create filters in a compact way, instead of creating individual calculated fields as filter or setting up advanced table calculations. Here is a workbook comparing various ways of implementing the same filter.

       

       

      Sum(count of orders)2.png

       

      SUM(count of orders).png

      Simon Runc

      Simon, could you please help me to have a look at it  when you have time ?

       

      http://vizdiff.blogspot.com/2015/12/the-order-of-operations-within.html#!/2015/12/the-order-of-operations-within.html

        • 1. Re: Confusing values of the measure filter
          Simon Runc

          howdy!

          In the sheet 'Solution by Measure + Table Calc Filters' I do not understand why the sum(order of orders) is not the 5009 but the value range from 1 to 17?

          ...So this is because a FIXED LoD is returned at Row Level (at the specified level, Customer in our case). This means, although the value is determined by an Aggregate (COUNTD) the FIXED LoD, the result is run by customer (irrelevant of the level of detail in the Viz) and then returned at the customer level...So the dimension has values ranging from 1-17 (if this was just COUNTD([OrderID]) the range would depend on the VizLoD)

           

          In the below...I brought in COUNTD([OrderID]) as an Aggregate, but then added Order ID to the vizLoD...so the value for each Customer/OrderID is 1. However with the FIXED version it is always the  customers total COUNTD of Orders

           

           

          You might find this (attempt!) from me to explain the different calculation types (and how the VizLoD affects them useful)...Answer - Quora

           

          I've also attached (if you are Excel inclined) and example I use in my training, showing how an LoD differs from an Aggregate Calculation (btw this is just a way of explaining it, and NOT how it works under the bonnet!!)

           

          Let me know if any of that doesn't make sense.

          1 of 1 people found this helpful
          • 2. Re: Confusing values of the measure filter
            Yingying Zeng

            Simon Runc

            Thanks so much for the reply. Your explanation is awesome as always!

            I agree the interesting values is related to LOD. However, when I drag that filter(same value)- sum(order of orders) to the view. It added together. Please see the twbx  workbook" Same LOD in the view Set filter and dimension filter examples" /sheet LOD different in the view.

             

            Kindly let me know what I miss

             

            Also,Thanks  a lot for sharing your amazing training materials and I definitely need to learn it

            • 3. Re: Confusing values of the measure filter
              Yingying Zeng

              Simon Runc

               

              I rechecked the view, it seems that the label misleading me. The chart is same (order of count) but the label is sum(profit)...

              • 4. Re: Confusing values of the measure filter
                Simon Runc

                OK I think I see what you mean...this is about if you are using it as a dimension, or a measure. When it's a dimension, when you drag it into the view it will detail out all the unique values in the dimension (as it would do for something like Category...which is mentally a bit easier as it's a string!). However when we use it as a measure, it's going to aggregate the results to the Level of Detail in the Viz.

                 

                Think about a store number (storeCode) that's an integer. When it's used as a dimension, when you drag it into the canvas you'd expect to get a row per store ID (and you do). However as it's a number we could SUM it (it makes no sense, but we could)...in this case (as the number means something) it makes sense in both cases. In Tableau we can use the same field in multiple ways (even in the same sheet)...and that's not even mentioning that as well as Measure and Dimension, we can use it as a discrete (blue) and continuous (green) version of each!!

                 

                So when used as a measure...as we have defined the Level of Detail ({FIXED [Customer Name]....) at the Customer Name Level, when we also have customer in the VizLoD it will return for each customer the COUNTD of their orders (we've asked it create the calculation at the Customer Level, and then SUM it with customer in the Viz...so it would be the same if you changed the aggregation to MIN, MAX, AVG). Try it and see! However when we don't have Customer in the view (and used as a measure) it will add up (SUM) each individual value for each customer (even if each customer is actually many rows...as in superStore, where each row represents an OrderNumber and Product). As we have *told* Tableau the Level at which to run the calculated field at, it will just SUM the values for each customer (not each row)...it's actually pretty clever stuff!! (this is also why it can be in-efficient as it needs to create a sub-query each time it's used...although this has got better since it's launch and it's clever enough to workout when it doesn't need to create the sub-query and just use a regular aggregation....but that's a whole different rabbit hole!!). In this case the MIN, MAX and AVG would be different (as we don't have customer in the VizLoD)...again try it and see what it returns each time.

                • 5. Re: Confusing values of the measure filter
                  Yingying Zeng

                  Got it!!! Thanks a lot Simon. You are amazing!!!

                  Happy Friday!!!