
1. Re: Rolling 8 Quarter Weights in Tableau
Joe Oppelt Feb 23, 2018 6:37 PM (in response to Dustin Greelis)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 xmany 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 8quarter 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 Feb 26, 2018 9:44 AM (in response to Joe Oppelt) 
3. Re: Rolling 8 Quarter Weights in Tableau
Dustin Greelis Feb 26, 2018 9:53 AM (in response to Joe Oppelt)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 "2year" in the title). So basically, the goal is to put the weight for 2012 associated with 20122013 to that time period, and the weight for 2012 associated with 20112012 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 20122013), and "Late" for when the year is the later of the 2 years (i.e. 20112012). 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 Feb 26, 2018 9:56 AM (in response to Dustin Greelis)I can't wrap my head around "the weight for 2012 associated with 20112012". Given the numbers in the screen shot, what value would be the weight for 2012 associated with 20112012, and how would you calculate it?

5. Re: Rolling 8 Quarter Weights in Tableau
Dustin Greelis Feb 26, 2018 11:32 AM (in response to Joe Oppelt)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 2year 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 20122013 data point, her data in "Roll8_Weight_Early" would be used, since 2012 is the early year out of the two. To create the 20112012 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 Feb 26, 2018 11:44 AM (in response to Dustin Greelis)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.

SampleTableauWorkbook A.twbx 3.1 MB


7. Re: Rolling 8 Quarter Weights in Tableau
Dustin Greelis Feb 26, 2018 11:57 AM (in response to Joe Oppelt)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 Feb 26, 2018 12:07 PM (in response to Dustin Greelis)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.)

SampleTableauWorkbook B.twbx 3.1 MB


9. Re: Rolling 8 Quarter Weights in Tableau
Dustin Greelis Feb 27, 2018 12:33 PM (in response to Joe Oppelt)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!

SampleTableauWorkbook C.twbx 3.1 MB


10. Re: Rolling 8 Quarter Weights in Tableau
Joe Oppelt Feb 27, 2018 12:54 PM (in response to Dustin Greelis)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.

SampleTableauWorkbook D.twbx 3.1 MB


11. Re: Rolling 8 Quarter Weights in Tableau
Dustin Greelis Feb 28, 2018 7:44 AM (in response to Joe Oppelt)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.