1 2 Previous Next 16 Replies Latest reply on Jun 27, 2017 10:42 AM by Evan McLain

    Question on how to create a product velocity calculation (and Joe Mako's Order of Tableau Filter Operations)

    Rust Cadwallader

      So I feel like there should be a fairly easy way to do this.

       

      What I am trying to do is create a 'bucket' for customers based upon the # of transactions that customer had.

       

      I can manually determine the breakpoints, so I'm not looking for any complex calculation there.

       

      But what I can not figure out is how to get the count of records by customer_id in a calculation

       

      I want to be able to say

       

      If x > 50 then 'High'

      else if x > 10 then 'Medium'

      else 'Low'

       

      but I am not certain if you can calculate 'X'

       

      I know you can do this by manually adding them to groups, but I was hoping not to go that direction.

       

      And I need the 'High', 'Medium', 'Low' available in views, so I do not think a set is appropriate.

        • 1. Re: Question on how to create a product velocity calculation
          Mike Hyde

          Do you just need to count up the number of transactions by customer and then assign a label?

          If so, you can use a calculated field with:

           

          SUM([Number of Records])

           

          to calculate the number of records.  If you arrange the view so that the data is split by customers, then it will calculate the number of records for each customer.  Whenever you use an aggregating function like SUM() in a calculated field, it will aggregate according to however you layout the sheet in question.  In this example, you could then assign the labels using an IF statement, eg:

           

          If      SUM([Number of Records])>24 THEN "High"

          Elseif  SUM([Number of Records])>20 THEN "Medium"

          Else    "Low"

           

          END

           

           

          See attached for example.

           

           

          Rgds

          Mike

          • 2. Re: Question on how to create a product velocity calculation
            Rust Cadwallader

            The problem is I do not necessarily want to split the data by customers

             

            I want to basically classify each customer as 'High', 'Medium' or 'Low' (think Group) and then use that value in the view

             

            So that I could I would see

            Region1 --> High --> C1

            Region1--> High --> C2

            Region1 --> High --> C3

            Region1 --> Medium--> C4

            Region1--> Medium --> C5

            Region1 --> Medium --> C6

            Region1 --> Low--> C7

            Region1--> Low --> C8

            Region1 --> Low --> C9

            Region2 --> High --> C11

            Region2 --> Medium --> C13

             

             

            I know you can do it using groups, what I was wondering was if there was a way to do this using Calculations.

            • 3. Re: Question on how to create a product velocity calculation
              Joe Mako

              You could be looking for a custom table calculation. If you can provide a sample data set and detail what you expect for a result and interactions you want to allow for, an example can be created for you.

              • 4. Re: Question on how to create a product velocity calculation
                Rust Cadwallader

                Use the Sample Coffee Chain data set.

                 

                What I want to be able to do is create a calculation for Products:

                 

                if a product sold more than 400, it is high

                if a product sold 200 - 400 it is medium

                if a product sold less than 200, it is low

                 

                And that classification stays with the product, regardless of other filters, slicers, etc

                 

                I know this can be done creating a second datasource and linking the data sources via the product, where the secondary data source has:

                 

                if sum([Number of Records]) > 400 then "High"

                elseif sum([Number of Records]) > 200 then "Medium"

                else "Low"

                end

                 

                And then dropping that calculation on a view with the product from the original.

                 

                 

                But is there some other way to accomplish this using table calculations that does not involve duplicating the data source

                • 5. Re: Question on how to create a product velocity calculation
                  Richard Leeke

                  Yes, you can do that.  Sheet2 in the attached.

                   

                  I'm not sure which way is best - that might depend on data volumes and distribution.

                  • 6. Re: Question on how to create a product velocity calculation
                    Mike Hyde

                    Hmmm that "Late State Filter" is some special kind of voodoo.  You're using some derived table calc to act as a filter in such a way that the entire data set is included when the first table calc (total sales for all states) is evaluated?

                     

                    I'm guessing this isn't really documented anywhere...?

                    • 7. Re: Question on how to create a product velocity calculation
                      Richard Leeke

                      Yeah, I intended to explain that when I posted that example but got interrupted.

                       

                      Filters on calculated fields which involve table calculations are evaluated by Tableau after all table calculations have been evaluated (unlike normal filters which are passed to the database and restrict the rows returned to Tableau).

                       

                      So the voodoo is just a trick to force a filter that you really just want on a plain old field to be evaluated after table calculations have been evaluated.  I just called it "Late State Filter" because I want the filter evaluated later than everything else.  The trick is just to use the LOOKUP() table calculation function to lookup the value in the current row (LOOKUP([X], 0) just says lookup the value of [X] that is zero rows away from the current row).  You never need to worry about setting the partitioning on that field because the current row is always the current row, whatever your partitions are.

                       

                      I've never seen it documented, I think I thought of it for myself (or maybe I got it from Joe).  It's a useful trick which often comes in handy.

                      • 8. Re: Question on how to create a product velocity calculation
                        Mike Hyde

                        That is an excellent trick for sure.  There are several times when I wanted to do that, and often ended up using "hide" to visually remove some items but keep them included in the rest of the calculations.  This is much better than hiding things though, as the problem with hide is that if your data changes and a new value appears then the new value will not be hidden by default, causing some visuals to mess up.

                         

                        Joe used this in an example yesterday as well, so thanks for explaining!

                         

                        Having some kind of transparency around calculation order and table calc setups (partitioning and sorting) would be a great improvement to Tableau.  It's the one area of Tableau that still feels like voodoo to me whereas they've done a terrific job of making most other things very transparent and intuitive.

                         

                        Thanks again

                        Mike

                        • 9. Re: Question on how to create a product velocity calculation
                          Rust Cadwallader

                          So I like the trick.

                           

                          However, my goal was to be able to get a value that would never change, regardless of how you slice the data.  If I go into your view and add a filter on market size to 'Major', the sales change.  Most of the time, this is what you would want, but in this case, I want to know the overall total sales, not just the sales within the filtered data.

                           

                          I need a way to tell the calculation not to take into account the filters, slicers etc.

                           

                          For example, Cafee Latte is 'Medium', and is always in the 'Medium' total sales grouping regardless of any other factors because when you take the data as a whole, that is where it falls.

                           

                          Linking the datasources gives me that because the linked data source doesn't appear to take into account the filters and I get the 'whole' values.

                          • 10. Re: Question on how to create a product velocity calculation
                            Richard Leeke

                            Rust: Yes, the linking approach is much better from that perspective.  I use blending back to the same datasource for that sort of thing quite a lot.  I was just demonstrating what you have to do to make it work with table calculations.  But you would have to apply the "late filtering" trick to every filter you wanted to apply to the sheet, and also add that field as a dimension to one of the shelves if not already there to make the table calculation approach work the way you want.

                             

                             

                            Mike: I agree about the lack of transparency of order of operations.  Joe has made several impassioned pleas for that on the forums over the last year or so - and in fact there is a thread somewhere where one of the Tableau folk (maybe James or Robert I think) made a very good start on that - but the thread never pinned down some of the finer points.  So there's still a lot of voodoo.

                             

                            The whole topic of Table Calculations still has lots that is unexplained for me.  There was a monster thread I started on table calculations about a year ago in which Ross Bunker from Tableau explained quite a lot of that - and left us with tantalising hints of lots more explanation to come in an advanced table calculations document he was writing - but I don't think that ever saw the light of day.  It's almost as if there is a reluctance to document the hard stuff adequately for fear of confusing people (so instead we get left to confuse ourselves on our own).

                            • 11. Re: Question on how to create a product velocity calculation
                              Joe Mako

                              Here is my current working guess on an order of filter evaluation:

                               

                              1. Context Filters - temp table in data source, always evaluated first

                              - Note that if you have a calcuated field that is dependant on a parameter in use as a context filter, changing the parameter value will not cause a regeneration of this temp table.

                               

                              2. Top and Condition Filters - In the filter dialog for discrete dimension pills (blue pill, non-agg), you have two tabs for Top and Condition. When these are set, they will generate a sub query and join with the data before the where clause is evaluated.

                               

                              3. General Filters on Non-Aggregated Pills - This includes pills that are dimensions (blue or green pills, non-agg), ad hoc groups, bins, sets and action filters. These are evaluated in the where clause of the query sent to the underlying data source.

                              - Note that your non-aggregateion calcuation formulas will be evaluated before this point, so you could consider it filtering with a IF statement in a formula that will be evaluated before these normal and context filters.

                               

                              4. Filters on Aggregated Pills - This includes aggregrations that are a continious measure (green pills, with agg). You cannot place a pill that is a discrete aggregation (blue pills, with agg) on the filter shelf. The work around is to use a calcuated field and hare the test condition performed in an IF statement that returns a number.

                               

                              5. Filters on Custom Table Calcuations - All Table calculations will be evaluated before filtering on custom table calcuation pills is applied. Generally this is the latest useful filtering

                               

                              6. Other Late Filtering - you can filter on the fields "Latitude (generated)" and "Longitude (generated)" and filtering on them will be applied after table calculations are evalated. Also you can select dimension values in a view and select "Hide" from the right-click context menu. While this Hide operation is not really a filter, it will remove the display of the marks in that dimension value from view after table calcuations have been evaluated.

                               

                              7. Pages shelf - I think of the pages shelf like creating a "table shell" that only displays the marks for the current selection. The panes/cells and axis ranges will be bases on all possible page selections. A dimension pill on the pages shelf will be available to table calculations because the pages shelf filtering happens after table calculations are evaluated.

                               

                              Additionally of note, you can effectivly filter data inside of the formula of a calcuated field, by using things such logical or User functions, and those will be evaluated depending on your formula, either before context filters, after aggregration, or after table calcuations.

                               

                              This is my current understanding, and I welcome your corrections if I am incorrect in any of these points.

                              1 of 1 people found this helpful
                              • 12. Re: Question on how to create a product velocity calculation
                                Jonathan Drummey

                                Months later this continues to be a reference I return to. Thanks for all your work on this, Joe!

                                 

                                One point to add, based on what I found in the course of researching issues on this post: http://community.tableau.com/thread/115881. The default reference line calculations (the ones you get by adding a Reference Line and using Tableau's default options) appear to occur sometime after 5. Filters on Custom Table Calculations. I found this when I was using a filter based on INDEX() and Tableau was doing the reference line on the post-filtered data. In my case, this was an issue because I wanted the mean and standard deviation calculated at the level of detail, however (based on the filter) Tableau was calculating reference lines based on the reduced data set.

                                • 13. Re: Question on how to create a product velocity calculation
                                  Matt Lutton

                                  Bump so others may find this reference easily -- it still helps me regularly

                                  1 2 Previous Next