13 Replies Latest reply on Feb 12, 2013 7:46 AM by Jonathan Drummey

    Trouble getting consistent results on a table calc with different views.

    Justin Larson

      What I have here is a table calculation computed along one field. it works fine when I reorganize the pills and layout, but it breaks when I add dimension pills to the view. What I think I'm trying to figure out is how I can structure the formula to respond only to filters. I want to be able to add any additional dimensions to the view without changing the outcome of the formula. In specific, this formula is being used to color a chart, and I want to be able to see the same colors used to color the dots of customers on a map.

       

      The specifics are particularly hard to describe, but I'll do my best, in conjunction with a simplified sample workbook I've whipped up to demonstrate. This is a segmentation projection in which we are using this workbook to target and rank customer segments. The idea is that the segments are identified as highly important or notsomuch by comparing aggregations of customers. 

       

      So, goal here: color the dots on the map by calculated field "Segment Strength" matching the outcome from the same calculation on the profile chart.

       

      1) Data description

      Union query

      part one of the query is every possible customer.

      Each line is a customer with an ID; segment is an attribute of the customer and each line is marked as "Base"

       

      part two of the query is what we'll call "orders",

      Each line is an order, which is marked "target"

      this part includes customers (with their ID), plus a product they ordered and a volumetric field used for aggregation

       

      2) How the calculations look

      Compare each segment's percent of total in base to each segment's percent of total in target. (this comparison is the target index0

      For target, the metric used for percent of total calculation is the Volumetric field. For base, it's 1 per record.

       

      3) Point of the exercise

      each segment is evaluated using the "Segment Strength" calculation, which relies on Target percent of profile, and target index.

      this Segment Strength should be evaluated the same, no matter what is in the view (as long as the minimum level of detail is in the view, obviously)

      The profile tab shows this formula successfully calculating. I'm trying to get the same results on the map, or on a chart that contains more pills than just segment.

       

      Please comment and ask questions as necessary. It's difficult for me to know how much context is too much or not enough for this to make sense to an outsider. My live workbook contains substantially more data, and the idea is that I can apply any filter to the target to do a custom profile of, for example, "customers who bought product1," just as easily as "Customers who live in NYC," etc.

        • 1. Re: Trouble getting consistent results on a table calc with different views.
          Mark Jackson

          See if this works for you. You can't filter on type, and you needed to average your lat/long.

          1 of 1 people found this helpful
          • 2. Re: Trouble getting consistent results on a table calc with different views.
            Justin Larson

            That's really close.

             

            Let me see if I can understand what you did here. By taking an agg of lat/log, it removed them from partitioning altogether, which allowed the segment strength calculation to do its work. However, because you averaged the lat/lon, you had to add customer ID to level of detail to ensure that one mark appeared for each customer as before. Can't filter on type because base volumetric is necessary to calculate index, which is necessary to calculate segment strength (had a feeling that might be the case). As a work around, you filtered null values from the aggregate of the target volumetric to get just the Target marks.

             

            Now, theoretically, I could use any filter that only effects target part of data, and the profile and map marks will automatically reflect the changed segment strengths.

             

            So the only thing that this doesn't do on the map is segment strength coloring on Base marks or being able to toggle. I will experiment on setting up an aggregate calculation to drive a filter that works the way you set it up as an alternative.

             

            edit: I got the toggling base/target on the map to work by using a parameterized calculated lat/lon;

            if [Type]=[Target/Base Parameter] then [Longitude] end

            This transforms non-interesting marks into nulls, so they are still in the view, just out of the way, but for some reason the segment strength is still not calculating correctly for base marks on the map.

             

            Meanwhile, if I've interpreted this correctly, the cross tab that failed (where I dropped in products) could not be helped by this technique, yes? Any ideas on how I can get the same result with more pills in the view? Another things I've considered, but not tried yet is to set up a parallel data source with only the segments, and do the segment strength calculation there and blend back in. Do you think that would be a worthwhile approach? I don't have a lot of experience yet with the nuances of data blending and filtering, so I don't know what hurdles I'd run into.

            • 3. Re: Trouble getting consistent results on a table calc with different views.
              Mark Jackson

              You are correct in how it works. You could make this view much more flexible if you transformed your data to have only one record per customer. That way you wouldn't need table calculations. With table calculations, you have to specify the "Compute Using" to account for all the relevant fields in your view. So if you add pills to your view, you have to manually fix the "Compute Using". You might be able to conceive of a way around this by using parameters, but I think you'd just be making it unnecessarily complex. I say, just fix your data.

              • 4. Re: Trouble getting consistent results on a table calc with different views.
                Justin Larson

                Not sure what you mean that it would be more flexible with one record per customer, as I've found this format to be the most flexible for this type of workbook, but I'm curious for you to elaborate.

                 

                The sample workbook is a simplified view of my real workbook, which has a lot more detail related to each transaction, which is what I'm using for creating a "target". Those types of calculations are much better suited to a transactional dataset vs lateral, I've found. There are other considerations with my real workbook as well, such as the base and target coming from different sources that do not have a reliable PK between them. Ultimately, after having worked with this analysis a number of times (just notsomuch in Tableau), the Union Query has proven by far the most adaptable.

                 

                Ultimately, though, back to the original problem, I want the segment strength calculation to result in a value that is an attribute of each segment, which could then be placed in the view as if it were hard coded in the data, regardless of the view. The only thing that would change the result is changing the target, which will be accomplished through filters. The filters in the real workbook may not necessarily be as simplistic as filtering on a single field, but a combination of parameters that the user selects to effectively customize the "select" part of the target part of the union query.

                • 5. Re: Trouble getting consistent results on a table calc with different views.
                  Mark Jackson

                  What I mean is that your base and target values are distinct columns for each customer instead of creating two records per customer. Blending the data might be another option that could work if the suggestion to reshape your data isn't practical. You could have a base and a target data set.

                  • 6. Re: Trouble getting consistent results on a table calc with different views.
                    Mark Jackson

                    Now that I think about it more, you still will need to use table calculations. So I don't think you are going to be able to create the flexibility that you want for the reasons I stated above.

                    • 7. Re: Trouble getting consistent results on a table calc with different views.
                      Jonathan Drummey

                      Hi Justin,

                       

                      You wrote:

                      I want to be able to add any additional dimensions to the view without changing the outcome of the formula.

                       

                      This is not how table calculations work in Tableau. With table calculations, we set the Compute Using dimension(s) that define the addressing, i.e. what combinations of values we will calculate results for. All other dimensions in the view*** are automatically part of partitioning, i.e. the combinations of values that will cause the calc to "start over". My current understanding is that Tableau works this way to support the cycle of visual analysis: as we can drag and drop dimensions on the view to create new panes, Tableau will compute as we'd naturally expect for each pane created.

                       

                      Also, depending on where dimension pills are in the view, you can trigger densification behavior. That's part of what is going on in your "not so successful" crosstab view, by putting Product on Columns and Customer Segment on Rows, then there is a cell for every combination for Tableau to compute the table calcs over.

                       

                      *** "In the view" means dimensions on the Marks Card, Rows, Columns, and Pages Shelves. Dimensions on the Filters Shelf are not considered for addressing or partitioning in table calculations.

                       

                      It's also important to know that row-level filters and filters on aggregates are applied before table calculations are computed. I'm pretty sure whether a single filter is context or not will have no impact on table calc results (except perhaps to improve performance), though of course in combination with other filters context filters can have a tremendous impact. If you want certain results to remain the same no matter what filters are chosen, there are a few ways to do this in Tableau:

                       

                      - Use Custom SQL or query to generate a total outside of Tableau so that it is available to every row, no matter what is filtered.

                      - Use a data blend with custom relationships that will ignore the filters to get the total.

                      - Use table calculation filters, which are applied after most all other computations are completed.

                       

                      I haven't gone through this in complete detail, but as a general rule what Mark said about flattening the data and combining the base and target values is useful. Also, keeping the number of dimensions to a minimum is useful, wherever possible use aggregates, like Mark set up. Once a viz uses table calcs, further manipulation of the viz gets more complicated. Sometimes a little, sometimes a whole lot, this seems to be one of the latter. For this kind of view, I use the kind of thinking I describe in http://drawingwithnumbers.artisart.org/eddie-van-halen-and-dashed-lines/:

                       

                      1. Understand your goal.
                      2. Understand your data.
                      3. Figure out what the ideal data structure and values – given an understanding of how Tableau works – would look like to reach that goal.
                      4. Then and only then figuring out what calculated fields, dimensions, measures, and/or reshaped data would lead back to #1.
                      5. Build the view. Now you start dragging and dropping pills.

                       

                      Here's a starter: Ultimately, you want one dot per customer, so that means you're going to want the Customer ID in the view. There's only one Customer Segment, Latitude, and Longitude per Customer, so all of those can be aggregate fields and not affect your partitioning. However, you didn't specify what you wanted the totals to be computed over when Product is added to the view, so I'm not able to help you out there.

                       

                      Jonathan

                      • 8. Re: Trouble getting consistent results on a table calc with different views.
                        Justin Larson

                        So I understand why the formula broke on the "notsomuch" tab, what I'm trying to figure out is how I can structure the formula/pills such that the result comes out the same in spite of the new layout. At the surface level, I was trying to ascertain if there is a trick that can be used for a table calculation to kind of ignore the partitioning, or somehow go around it. The formula should calculate along customer segment no matter the view.

                         

                        From the sound of it, if I want the formula to result the same, I'd have to customize a formula for each view, based on what the partitioning looks like for that specific view.

                         

                        The SQL approach to this particular problem doesn't seem sound, as the result of the formula can't be calculated at the point of entry. It's not that I want the same results regardless of filters, it's that I want the calculation to respond to filters, but result the same no matter what's in the view.

                         

                        What's funny about this "problem" is that it's not a huge deal, it's just a matter of making a workbook have a very consistent look and feel. In the end product, users can successfully identify primary segments through the view that does work, and I can use that view on a dashboard with highlighting and filtering actions that are altogether functional. The minor difficulty was just in having the dots on the map (or marks on other views) match the color scheme of the chart view.

                        • 9. Re: Trouble getting consistent results on a table calc with different views.
                          Jonathan Drummey

                          Hi Justin,

                           

                           

                          So I understand why the formula broke on the "notsomuch" tab, what I'm trying to figure out is how I can structure the formula/pills such that the result comes out the same in spite of the new layout. At the surface level, I was trying to ascertain if there is a trick that can be used for a table calculation to kind of ignore the partitioning, or somehow go around it. The formula should calculate along customer segment no matter the view.

                           

                          You don't need a different calc for each view, that's part of the beauty of Tableau. Instead, you have different Compute Using's for the table calculations for each set of dimensions used in each worksheet. When there are more dimensions to be used for addressing that's where the Advanced... Compute Using comes into play (along with Restarting Every and At the Level should you need them). You can add the additional dimensions to right side of the Advanced... Compute Using dialog and that adds them to the addressing. Depending on the calc, this may cause extra results of the same value to appear such as for a WINDOW_SUM() calc, or create new values like for an INDEX() calc.

                           

                          I'm not sure what you mean by "The formula should calculate along Customer Segment," it seems from my investigation that it doesn't matter since there's only one segment per customer, is that the case?

                           

                          Jonathan

                          • 10. Re: Trouble getting consistent results on a table calc with different views.
                            Justin Larson

                            Yes, there is only one Segment per customer.

                             

                            What I'm meaning to say is that the calculation should be aggregating all lines with a common segment, and no other field should be creating additional partitions. Getting extra results of the same value is what I was expecting, but could not get the calc to return this way, even after messing with the advanced dialog box as I understand the way it works. I had thought that forcing it to compute using with the extra fields would do the trick, but I still was having trouble 1) predicting its results, and 2) getting the result I actually wanted.

                             

                            With this in mind, how would you structure the formula on the map (for example), where the calculation I want looks like:

                             

                            sum(target volumetric for segment related to this mark) / total (target volumetric for all segments).

                             

                            In this case, my expected result would be exactly what you described, the same result to show over and over in a crosstab view.

                            • 11. Re: Trouble getting consistent results on a table calc with different views.
                              Jonathan Drummey

                              Ok, here's where I think you were getting tripped up:

                               

                              1. When your goal is a value per Segment, the Segment is partitioning, not addressing. This can be confusing in Tableau, we often think of partitioning first but the way we control it is through seeting the addressing/Compute Using.

                               

                              2. While you did an initial validation in a crosstab view, the later views that had more dimensions in the view did not have their own crosstabs. This makes figuring out what is going on with table calcs a lot more difficult. My suggestion is to build a view where all your dimensions are on Rows, with the right-most dimension(s) being what is used for the Compute Using. That makes it really easy to see if you're getting the right values, with many calculations you can turn on totals & subtotals for validation.

                               

                              3. While SUM(target volumetric) works fine as the numerator when there's only Segment in the view, when you add additional dimensions to the view it fails because the view now has a finer level of granularity; alternatively, one could say there are more (smaller) panes. The solution to that is to build two table calcs, for example in the "workout" crosstab in the attached I set up a [Tgt % of Profile] as  TOTAL([Target Volumetric Sum])/[Tgt % of Profile Denominator], where the latter calc is also TOTAL([Target Volumetric Sum]). Tgt % of Profile Denominator has a Compute Using of Segment, Customer ID while Tgt % of Profile has a nested Compute Using of Customer ID. That way the Denominator calc generates a total across all Customers/Segments, and the Tgt % of Profile calc uses that value while calculating it's own total within each Segment. I built corresponding calcs for the Base % of Profile, and revised versions of the other calcs in the view, including a table calc for the filter.

                               

                              I chose TOTAL here because the table calcs didn't require any sorting and in v7 TOTAL doesn't need the optimization techniques for performance that WINDOW_SUM does.

                               

                              Hope this helps!

                               

                              Jonathan

                              • 12. Re: Trouble getting consistent results on a table calc with different views.
                                Justin Larson

                                That did it. I had a feeling I would need that extra layer of table calc, I just couldn't figure out how to structure it. Splitting out the numerator/denominator with total()... well, I just wouldn't have gotten there anytime soon.

                                 

                                Thanks again for taking the time to understand the problem, and helping out. I'm gonna' spend a bunch of time absorbing this workbook now, so I can deploy in the production workbook without overcomplicating it.

                                • 13. Re: Trouble getting consistent results on a table calc with different views.
                                  Jonathan Drummey

                                  You're welcome! When I started working with measure data with numerators & denominators that tripped me up, too.

                                   

                                  This makes a great example of how understanding the level of detail is necessary for correctly partitioning calculations, it's definitely worth coming back to!

                                   

                                  Jonathan