12 Replies Latest reply on Oct 25, 2016 3:33 PM by mark.welsh

    Window Sum calc causes change in Reference Line

    mark.welsh

      Can someone explain why adding a WindowSum calc to my view causes my reference line to change?  I'll post a workbook if needed, but it will probably take quite a bit of time to mock this up with Superstore data.

       

      Before adding windowsum:  After:

       

      I need to maintain the before, and I also need the windosum calc on the tooltip.  I'm using primary data source for the bars and secondary data source for reference line; blended on one field which is also added to color shelf.  WindowSum compute using is at the level of the blended field.  Reference line calculation is an LOD expression that is

      {fixed [Blended Field]: Sum([Secondary data source field])}

       

      I'm sure it makes sense to Tableau, but not to me!  Thanks.

        • 1. Re: Window Sum calc causes change in Reference Line
          mark.welsh

          Ok, I've recreated an example using superstore data and attached.  The first sheet "before run sum" shows what I want the reference line to look like.  It represents the budget line for all subcategories with sales for that month.  In June 2015 there were no Sales for Copiers, so the budget line is reduced to reflect that.  On the second sheet "add run sum" I want to add a running sum of sales by subcategory to the tooltip.  However, when I do that, the budget line for June 2015 jumps up to match the others.

           

          Can someone help explain why this is happening and help me find a solution?  Thanks!

          • 3. Re: Window Sum calc causes change in Reference Line
            Simon Runc

            hi Mark,

             

            I've got to admit defeat on the why this happens (I'm just going to ping the king of the "under the bonnet" Jonathan Drummey in case he's done some investigations on Table-Calcs, Reference Line and Blends...I suspect the number of "nuanced" things here is the reason, as these are the 3 most "obscure", from an "under the bonnet" perspective IMHO!!

             

            Fortunately, the solution is much easier!!...if you change the Running_Sum so that the compute-using is set to Sub-Category...you get the result you want.

             

             

            So it looks like the running_sum (table calc) needs to run over the level of the blend...in order for the blended calculation to pick up the lower MAX in June (where there are sales in one fewer Sub-Cats). As to exactly why? let's hope JD can enlighten both of us!!

            • 4. Re: Window Sum calc causes change in Reference Line
              mark.welsh

              Thanks Simon!  Here's my dilemma - I need the running sum to be computed Table Across or equivalent, so that the calculation returns the running sum at a subcat AND month level.  E.g. if I were to hover over Accessories for June 2015 I would see the sum total sales for Accessories for June and all months in the view prior to June.  At the same time I need another running sum that will return the total sales for ALL subcats by month.  E.g. hovering over any subcat for June 2015 would give me the sum total sales for ALL subcats for June 2015 and every prior month in the view.  So one running sum is by subcat and one is Total, but both are computed using the months in the view. 

               

              As soon as I change the running sum to Table Across my reference line of course flattens out again.  Is there a way to get all of the things i need!?

              • 5. Re: Window Sum calc causes change in Reference Line
                Simon Runc

                hi Mark,

                 

                Ah yes, I see!...so to get round this we have to set up the Table Calculation manually and use the "Restarting every" option. This is subtly different from addressing Month and partitioning on Sub-Class. The order is also important (else we can't restart on the level we want, sub-cat).

                 

                So here we are running the Table Calc over (addressing) Sub-Cat and (then) Month, which keeps the Reference Line running over the blend level (....hopefully Jonathan can enlighten us to exactly why!), but then we can set the restarting every to Sub-Category, so the Running Sum starts again for each Sub-category (so the same effect as Table Across). The below shows how I've set this up.

                 

                Hope that makes some sense and apologies for it being a "half-answer" in terms of the whys!...but when you have reference lines, table calcs and blends you're asking for trouble!!!

                2 of 2 people found this helpful
                • 6. Re: Window Sum calc causes change in Reference Line
                  mark.welsh

                  Perfect!  All seems to be working as planned.  Hopefully I don't have to add anything else that will cause it to come crashing down again!  Thank you sincerely for your help.  And one day I hope to actually understand hope table calcs work

                  • 7. Re: Window Sum calc causes change in Reference Line
                    Simon Runc

                    great stuff. Yes let me know if anything breaks and we should be able to account for it in the Table Calc set up. Yes Table Calcs are a bit like quantum physics, and as the great Richard Feynman once said

                    "If you think you understand quantum mechanics, you don't understand quantum mechanics."

                    only joking btw!!...they do get a little tricky to understand exactly why they behave the way they do in these complicated situations (especially when blended measures are involved) and you are getting fairly deep down the "rabbit hole" but for 90% of uses, they do as you'd expect. I'd advise you to watch the TDT videos on Table Calcs (there is 3 in total, near the bottom of this link ("TDT: Setting up for Table Calculation Success", "Tableau Calculations" and "Table Calcs Round 2 w J. Mako")

                     

                    The specified item was not found.

                     

                    Although they are a little old, the general principles give you a great grounding...2 hours of my time, I've got back 100 fold!!

                    1 of 1 people found this helpful
                    • 8. Re: Window Sum calc causes change in Reference Line
                      Jonathan Drummey

                      Because this involves reference lines, table calcs, and blends it’s going to take me awhile to figure out what’s going on and then do a  writeup, it might take me a couple of days to get to it.

                       

                      Jonathan

                      • 9. Re: Window Sum calc causes change in Reference Line
                        Simon Runc

                        Thanks Jonathan...yes when these 3 get together, we're getting into a Tableau version of "twister"...we can see which hands/feet are on which colours, but who's hand/feet belongs to who (and where they came from!!) is a different story!!

                         

                        Any light you can shed on it, is much appreciated!...and no rush (we've got the end behaviour as the OP needs), and sure you're busy prepping for your TC16 talk!!

                        • 10. Re: Window Sum calc causes change in Reference Line
                          Jonathan Drummey

                          Here's my explanation:

                           

                          First of all the Superstore data is sparse, there's no data for Copiers in June 2015. The budget data is set up as an annual budget per Sub-Category and the blend is on Sub-Category so since there's no mark for Copiers/2015 that budget is not included and the reference line (which is defined as per cell) has the drop-out:

                           

                          Screen Shot 2016-10-25 at 2.48.36 PM.png

                           

                          Correct me if I'm wrong here: My assumption is that the budget line should *not* have the drop in July 2015 since there's a $4.4K Monthly budget for Copiers...the fact that there aren't any sales for Copiers in July 2015 *shouldn't* remove that $4.4K from the budgeted amount displayed in the reference line. The total budget in the budget data is $1,118,000, so without that $4.4K for Copiers/July 2015 is makes it look like the total budget is smaller than it actually is.

                           

                          In order to deal with the sparseness of the data to ensure that the reference line is accurate we need to pad out the data in the primary (Superstore) source *before* the data blend, one way that can happen is through Tableau's data densification because in Tableau's order of operations data densification happens before blending. And this is exactly what happens when the first running sum is brought in with the default Table (Across) addressing, the Copiers/July 2015 mark is padded in before the blend and then the SUM(Monthly) measure from the blended budget source is added for Copiers/July 2015 and the reference line goes straight across.

                           

                          In Simon's view where he set the addressing of the running sum to Sub-Category & Month, partitioning on Sub-Category that turns off the data densification and that's why the budget reference line for July 2015 drops again.

                           

                          Understanding that data densification is happening has gotten harder in Tableau v9.3 and v10.0 because of the changes in Tableau's behavior, if there wasn't a reference line at all it wouldn't be visible in the view at all unless we changed other settings. See When 576 = 567 = 528 = 456: Counting Marks | Drawing with Numbers for more details on this.

                           

                          In any case, in reading through this there was a request for two running sums, one for each Sub-Category per month and an overall one. I set this up in the attached where I dragged the running sum quick table calculation over to the Measures window to create an RS per Sub-Cat measure. Then I created a new RS Overall calculation that was WINDOW_SUM([RS per Sub-Cat]) and brought that into the view with a *nested* compute using on Sub-Category so it sums the running sum (which has the Table (Across) compute using that is effectively on Month and partitioning on Sub-Category) across all the sub-categories for each month:

                           

                          Screen Shot 2016-10-25 at 3.05.38 PM.png

                           

                          Jonathan

                          3 of 3 people found this helpful
                          • 11. Re: Window Sum calc causes change in Reference Line
                            Simon Runc

                            ...ah so it was the densification turning off, depending on the running_sum compute dimension(s), which affected the blend...and thus the change in the reference line.  I guess we have stumbled across another way to pick it up (now that the marks count can be misleading on the densification on/off status).

                             

                            Thanks Jonathan...an education, as always

                            • 12. Re: Window Sum calc causes change in Reference Line
                              mark.welsh

                              Wow, thanks for the in depth answer Jonathan.  I'll study it when I have some time.

                               

                              I don't know how to quote part of a message, but for this portion: "Correct me if I'm wrong here: My assumption is that the budget line should *not* have the drop in July 2015 since there's a $4.4K Monthly budget for Copiers...the fact that there aren't any sales for Copiers in July 2015 *shouldn't* remove that $4.4K from the budgeted amount displayed in the reference line. The total budget in the budget data is $1,118,000, so without that $4.4K for Copiers/July 2015 is makes it look like the total budget is smaller than it actually is.

                               

                              I understand why you would think this way as that is how a traditional budget works.  However, with my actual data set I'm using hospital clinics that were not opened for the entire time frame in the view.  If the budget line doesn't adjust for clinics that were closed one year ago it will be overstated by that clinic's budget, which did not exist at the time.  So it's a little different than the superstore data example shows.  A clinic will always have greater than zero "sales" assuming it's actually open.  Hope that makes sense and at least addresses that portion of your reply.