14 Replies Latest reply on Sep 26, 2014 11:47 AM by Jonathan Drummey

    Historical Chart on WINDOW_AVG data not working



      I have a table with Dates, Client and Volume.

      For each date, I want to compute the Average of the Avgerage volume of each client (I really mean avg of avg !)

      for this I am using WINDOW_AVG(AVG(Vol)) and then I am using a filter on INDEX()=1 to just display the first row. (all this done accross Client)

      I get a nice table.

      The problem comes when I want to draw a chart.

      I still need to put Client on the detail, but then the lines are drawn for each client on the first row...


      any idea to go around this ?



        • 1. Re: Historical Chart on WINDOW_AVG data not working

          Hi Laurent,


          Is this what you're looking for?



          • 2. Re: Historical Chart on WINDOW_AVG data not working
            Matt Lutton

            What are your expected results?  If you make WEEK(Date) into a Discrete Dimension, you'll see this:


            • 3. Re: Historical Chart on WINDOW_AVG data not working


              I feel a bit stupid that it was that simple

              I did not guess it had to do with that...


              • 4. Re: Historical Chart on WINDOW_AVG data not working
                Matt Lutton

                Not at all... this is one of those cases where experience and experimentation with Pill Types comes in handy.  I didn't know it was going to do that either, until I did it...  I'm still not sure why it behaves differently for a discrete WEEK pill to be honest.  Someone with more experience and expertise may be able to tell us -- but you're sure those are the accurate results you're looking for?


                Jim Wahl -- is this something you can help with, in terms of understanding the different output based on the WEEK pill we choose?  Trying to spread out my "pings" today, across different gurus!

                • 5. Re: Historical Chart on WINDOW_AVG data not working
                  Jim Wahl

                  Absolutely no idea!


                  Although you can tell what's happening by turning on the mark labels -- tableau is connecting the dots of the INDEX()==1 client.




                  And, interestingly, you can get it to work properly by enabling Stack Marks from the top menu bar > Analysis.


                  • 6. Re: Historical Chart on WINDOW_AVG data not working
                    Matt Lutton

                    Thank you sir.  Seems odd to me, but I'm sure it has something to do with the Table Calc!  Perhaps when the WEEK(Date) pill is Discrete, it is automatically added to partitioning in the Table Calc??  I don't know if that is an accurate description of what is occurring, visually, but I'd sure like to know why this is happening in this particular example.



                    • 7. Re: Historical Chart on WINDOW_AVG data not working
                      Joe Mako

                      Jim, Nice find of Stacked Marks being a way to address this!


                      Another route that I commonly use is a combined field of the dimensions on the Path shelf, as demonstrated in Tableau Request Live - User Retention Rate on Vimeo (should start at the 29:20 point)


                      Also see the attached for this applied to your workbook.

                      • 8. Re: Historical Chart on WINDOW_AVG data not working
                        Joe Mako

                        To answer your question of why does changing between a Discrete (blue) and Continuous (green) pill impact the connectivity of the line, the reason is because of Data Densification. Jim's route of turning on Stacked Marks, is also turning on Data Densification. The flavor of data densification here is Domain Completion, all potential combinations of Client and Week of Date are generated by Tableau.


                        In your data source, there are only 14 actual combinations represented in the underlying data, but 6 possible Week of Date, and 6 possible Client, so a complete domain would be 36 marks = 6*6.


                        If you remove the table calc filter from the original provided 'ChartFailing' sheet, you will see '14 marks' in the status bar. Then when you turn the WEEK(Date) to a discrete or turn on stacked marks, you will see the status bar report '36 marks'.


                        The route I offer in the other comment with a Combined Field does not turn on Data Densification. Not turning on Data Densification can be desirable if you have very sparse data, for example 10,000 possible clients, and 400 possible dates with only 20,000 actual values, combined fields keeps the data at 20,000 records, while data densification would have Tableau crunch data for 4,000,000 records =10,000*400. At larger scales this becomes an even heavier computation burden, slowing the views and interaction.


                        Data Densification is a very deep rabbit hole in Tableau, and I am happy to talk about it at length with anyone interested in more details, you can find my email in my profile.

                        • 9. Re: Historical Chart on WINDOW_AVG data not working
                          Matt Lutton

                          This is fantastic stuff, Joe -- thank you!  And Jim, I just caught the Mark Stacking bit -- I missed that the first time 'round.


                          I'm still a bit confused about the impact of the INDEX==1 (or I actually used IF FIRST()==0 THEN WINDOW_AVG(Avg(Vol)) when I rebuilt this);  with this "filtering" in place, the number of marks (6) is the same whether the pill is continuous or discrete:


                          So, I may be missing the point of what you're saying about densification based on the Discrete or Continuous version of the pill. We can discuss on the phone as well, if you'd prefer

                          • 10. Re: Historical Chart on WINDOW_AVG data not working
                            Matt Lutton

                            For the benefit of laurent.carlier and anyone else viewing this thread:


                            I just spoke to Joe on the phone and he shared his screen with me for 1 hour and 3 minutes; we started discussing this thread, and we ended up discussing quite a bit more.


                            I mention this, just so others can be aware of how helpful others in this Community can be.  Joe has been super-generous with his time, and the knowledge he can share is vast and mind-blowing.


                            What would Joe Mako do?  He'd probably call Joe Mako. 

                            • 11. Re: Historical Chart on WINDOW_AVG data not working
                              Jonathan Drummey

                              When Joe emailed me on this yesterday I had a ***? moment. *Every* time I think I have a handle on densification sooner or later there's some new wrinkle on it. I'd always used combined dimensions to get marks to connect in lines, and never looked at stack marks in this case. Neat discovery, Jim!


                              Yesterday I'd written Can I add rows to underlying data from Tableau? about the six kinds of densification I know about, this adds a seventh to the list:


                              crosstab domain completion - this occurs when there are discrete dimensions on opposing Shelves (Rows, Columns, Pages), sparse data (not all combinations of those dimensions exist), and a table calc with the right Compute Using (which is most of them).


                              date domain completion - Tableau automatically completes the domain for discrete date dimensions when there's a table calc with the right compute using, usually one that is on the date dimension.


                              line and area mark type domain completion - An automatic domain completion whenever line or area marks are used with a discrete dimension on the end of one of the Rows or Columns Shelves, this is what enables the Format->Special Values options. There's a special bonus version of this if you have a dimension (copy) on the Level of Detail Shelf that Joe discovered, Tableau doesn't recognize that the domains are the same so it pads out the domain on itself.


                              stack marks for line and area mark types domain completion - An automatic domain completion occurs when there are line or area marks used and Analysis->Stack Marks->On is set.


                              Show Empty/Rows Columns domain completion - There are some special cases where Show Empty Rows and/or Columns can trigger domain completion, this might have been turned off in 8.2.


                              At the Level domain completion - There's a special case when a dimension used for At the Level is put on the Level of Detail Shelf where domain completion can be triggered. There must be dimensions on Rows/Columns/Pages to trigger this.


                              domain padding aka Show Missing Values - This is a different kind of padding where Tableau will fill out the missing values for a "range aware" dimension pill - a date, datetime (except a raw datetime pill), or Bin when we turn on the Show Missing Values option for that pill.


                              Besides turning off Show Missing Values, the solution to turning off unwanted densification is to put as many (perhaps all) of the discrete dimensions on Rows/Columns/Pages onto the Level of Detail Shelf, and use aggregate equivalents like ATTR() or MIN() in their place.


                              @Matthew - The reason why you're only seeing 6 marks is that you have a table calc filter in place that is only returning one address from each partition (the week). Densification could be adding 1 million marks to each partition (in this case it's adding a total of 22 marks across all partitions), but they are all getting filtered out. That's why in my Think Data Thursday TDT with Jonathan Drummey - July 10, 2014 I'd talked about putting on table calc filters as a last step or as late in the process as possible, because Tableau could be doing something "under the hood" like densification that wouldn't be visible except as potential changes in calculation results (or, in the case that Jim found, line marks actually connecting).


                              Another point on this is that since densification is adding addresses/rows to each partition, that can change what results come from calculations. For example SUM(Sales) on a densified address is going to return Null because SUM(Sales) is computed in the data source, or a LAST() calc might be returning an offset of 364 instead of an expected 3 because so many addresses have been added.



                              • 12. Re: Historical Chart on WINDOW_AVG data not working
                                Matt Lutton

                                @Jonathan:  Thank you very much--I need to review your last TDT video after all of my recent Table Calc discussions and discoveries.  So much of this stuff is interrelated, and its difficult (as you know) to keep track of all the various scenarios and factors.


                                I cannot thank you enough for your documentation on these topics.


                                I really appreciate your comments here, as Joe was able to SHOW me what was happening over a screen share last night, but your text description makes sense to me as well.  That is a good thing


                                I now see the huge impact/value in putting Table Calcs on the filter shelf as a last step.


                                One thing I have done in the past is to use IF FIRST()==0 (or INDEX==1, LAST==0) inside my actual calcs and I'm starting to see the benefit in simplifying the calcs themselves, and using the Filter separately.  Do you find that you keep the filter separate from the calc in most cases, and is this a personal choice based on simplicity, or are there factors that impact whether or not you'd use these inside the calc, or separately on the Filters shelf?

                                • 13. Re: Historical Chart on WINDOW_AVG data not working
                                  Jonathan Drummey

                                  @Matthew - You’re welcome!  To answer your question, there are two cases where I’ll use IF FIRST()==0/etc.. One is to return only a single result in a partition to other table calcs in (i.e. the table calc with IF FIRST()==0 is a child of a nested calc), the other is to filter out unneeded marks from the display. In the former case, we don’t really have any other option at this point (though Tableau is working on that, I’m hoping we see some results in v9). In the latter case (filtering out unneeded marks), I’ll do the IF FIRST()==0/LAST()==0 in the calc in order to keep the number of calcs down and the number of addressing & partitioning settings lower. Ctrl+Drag to copy a calc to the Filters Shelf and choose Non-Null values is a lot faster than making sure I’ve got the same exact Advanced Compute Using. Plus if I find I need to change the addressing of the original calc and the filter, I only need to change one compute using rather than two.


                                  However, as I’m building a calc I may not add the IF FIRST()==0 part until the very end, as it’s effectively filtering results and I’ll want to make sure I know what’s going on.


                                  That’s my own preference based on my own work habits, some people may find the viz easier to interpret and maintain with separate calcs.



                                  • 14. Re: Historical Chart on WINDOW_AVG data not working
                                    Matt Lutton

                                    This is very helpful;  thank you once again!