11 Replies Latest reply on Feb 28, 2018 7:44 AM by Dustin Greelis

    Rolling 8 Quarter Weights in Tableau

    Dustin Greelis

      Hi guys,

       

      I have an incredibly complex question and can't seem to find the answer or figure it out myself.  I work for a market research company, and we weight all of our data.  We have a weight for 4 quarter data (just 1 year at a time), and we have a weight for 8 quarter data (2 year's worth of data).  If you're not familiar with weighting, please check out this resource: Weighting - Market Research

       

      In order to apply 4 quarter weights, I just drag the "Weight" variable (which is a column of data in Excel) into middle of the worksheet and the "Year" variable into columns and it counts all of the weights for each year.  Now, here's where things get tricky... for 8 quarter data, each year appears twice (2012-2013, 2013-2014, 2014-2015, etc.).  AND, each time period has a separate weight!  So 2013 in the time period of 2012-2013 has a different weight than 2013 in the 2013-2014 time period!  Yikes!

       

      This is WAY over my head in Tableau.  We figured it out in our cross-tabulation software by: making each year its own field, splitting up the weights by year and making those their own field, applying the weights by year to each year field, then combining them.  Not sure if that's even possible in Tableau.

       

      If you just have any ideas on how we could possibly do this, please forward my way.  Your help is GREATLY appreciated in this task.

      Thank you,

      Dustin

        • 1. Re: Rolling 8 Quarter Weights in Tableau
          Joe Oppelt

          Create a sample workbook with some sample data in excel.  Do FILE -> "Extract Packaged Workbook"

           

          and upload the .TWBX file you get from that.

           

          I need to see what your data looks like.  You can do table calcs and tell them to look back x-many marks (so a WINDOW_SUM(Sum([Sales]),-7,0) would sum up the last 7 quarters plus the current one (if your data on the sheet is partitioned into quarters) and you can do your math for an 8-quarter span that way, quarter after quarter.

           

          I can show you if I had your working example.

          • 2. Re: Rolling 8 Quarter Weights in Tableau
            Joe Oppelt

            OK, so I've opened your workbook.  You don't have any sort of quarterly dimension, so really an 8-quarter analysis is just a 2-year analysis.

             

            Here is what I see right now:

             

             

            What are you looking to do?

            • 3. Re: Rolling 8 Quarter Weights in Tableau
              Dustin Greelis

              Hi Joe,

               

              Thanks for the responses so far.  I've mentioned a lot about 8 quarters, but we're trying to put 2 years together (equivalent of 8 quarters... probably should have just put "2-year" in the title).  So basically, the goal is to put the weight for 2012 associated with 2012-2013 to that time period, and the weight for 2012 associated with 2011-2012 to that time period.  I labeled them "Early" for when the year is the earlier of the 2 years (in our example, for 2012 it would be 2012-2013), and "Late" for when the year is the later of the 2 years (i.e. 2011-2012).  I guess that changes things a bit huh?  Hopefully it makes things easier!

               

              Thanks again,

              Dustin

              • 4. Re: Rolling 8 Quarter Weights in Tableau
                Joe Oppelt

                I can't wrap my head around "the weight for 2012 associated with 2011-2012".  Given the numbers in the screen shot, what value would be the weight for 2012 associated with 2011-2012, and how would you calculate it?

                • 5. Re: Rolling 8 Quarter Weights in Tableau
                  Dustin Greelis

                  So the only way I've found to use a weight for a particular year is to drag it into the middle of the table that Tableau builds, which sums up the counts for the weights.  Then I change the sum to a percentage of total (% down usually).

                   

                  For the 2-year time periods, this would have to be built in the table (or with calculated fields).  Basically, the same data point would be used twice.  Let's say person #12341234's survey was taken in 2012.  She is given a 15 for Early Weight, and a 18 for Late Weight.  So, to create the 2012-2013 data point, her data in "Roll8_Weight_Early" would be used, since 2012 is the early year out of the two.  To create the 2011-2012 data point, her data in "Roll8_Weight_Late" would be used, since 2012 is the later of the 2 years. 

                   

                  It's so hard to describe it over text rather than show in person.  Let me know if that doesn't make sense.  Thank you!

                  • 6. Re: Rolling 8 Quarter Weights in Tableau
                    Joe Oppelt

                    It doesn't make sense.  I don't want to have to learn a mathematical technique.  I want to know what you need to do with your numbers.  there are no person numbers or surveys in your data.

                     

                    Use the numbers and fields from the workbook.  I attached it with the sheet that I took the screen shot from.

                    • 7. Re: Rolling 8 Quarter Weights in Tableau
                      Dustin Greelis

                      Sorry, I missed that screenshot the first time.  Excellent way to display exactly what I'm talking about!  You shouldn't need to know any mathematical techniques. 

                       

                      Maybe this won't be so hard after all!  Looking at the late weights for 2002 in your screenshot (3,081k), and the early weights from 2003 (2,906k), is there a way to combine those into a single sum?  Maybe using a calculated field?

                      • 8. Re: Rolling 8 Quarter Weights in Tableau
                        Joe Oppelt

                        See attached.

                        The LOOKUP() function lets us look forward or backward in the table.  I used -1, which looks back one position.  You'll notice that the value for 2002 is null because there is no value in 2001.  We can force an insert of zero there if needed.  (Insert that whole LOOKUP chunk inside the ZN() function.  Likewise the SUM(Roll8...) portion of that calc if you want to force a value into 2017.)

                        1 of 1 people found this helpful
                        • 9. Re: Rolling 8 Quarter Weights in Tableau
                          Dustin Greelis

                          That's fantastic!!  Thank you so much!  I am able to use that for the overall sums.  Is there any way to look at the percentage of the total?  I attached a worksheet that shows percent of total -> table (down) for the roll8 early and late weights.  I can't seem to figure it out for the calculated variable too.  Is this possible? 

                           

                          Thanks again Joe!

                          • 10. Re: Rolling 8 Quarter Weights in Tableau
                            Joe Oppelt

                            I made a table calc that does a total of Calculation1.  Notice that for this calc I set the table calc settings to do TABLE(down) so that it adds up all the [Calc1] values in a given year.  I added it to TEXT.  Special note:  Calculation1 is called a nested calc in the [Total for Calc1] table calc.  In the settings for [Total for Calc1] you can set not only the [Total...] calc itself, but the nested calc.  See screen shot:

                             

                             

                            Make sure that [Calculation1] is still being evaluated as TABLE(across), even though [Total...] is evaluating Table(down).  (And because Calculation1 was already on the detail shelf when I added [Total...], the [Total...] calc picked up the settings of Calculation1 automatically.  But there are times when you have to reset the nested calcs properly, so I am drawing your attention to this detail now.)

                             


                            Then I made another calc to divide the [Calc1] for each row by the [Total...].  Because it invokes table calcs, it is treated as a table calc itself (even though there are no actual table calc functions in the calc.)  Therefore it's wise to make sure that the nested calcs are properly set for [%Total...]. (And they were because they picked up the existing settings from those calcs on the detail shelf.)

                             

                            See attached.

                            • 11. Re: Rolling 8 Quarter Weights in Tableau
                              Dustin Greelis

                              Thank you so much Joe.  I learned a ton from what you posted!  And I have a lot to look into to learn more about this.  I didn't think this would be possible... very much appreciated.