Would you be able to attached the sample workbook as a twbx file? i tried opening the twb but there's no data.
Hi Stegmaier, attached the twbx file. My bad, thought I had. Thanks for looking!
This might work for you. The window_avg function:
Last 6 months (including the current month) - WINDOW_AVG(sum([Infections]/[Line Days]),-5,0 )*1000
Last 18 months (including the current month) - WINDOW_AVG(sum([Infections]/[Line Days]),-17,0 )*1000
Testdata.twbx.zip 36.1 KB
Thanks for your respone. The formula looks logical, but I don't see that it's taking only the sum of the recent 6 months and giving me the average? Maybe I'm missing something here? I checked out the formulas in your attached Tableau workbook, but depending on how the Month/Year is filtered, it's showing an average for every month, not just the recent 6 months (which isn't much different from my Monthly Rate). In theory, any months before the recent 6 should be blank, because I don't want to calculate on any other months but the recent 6 (Aug2012-Jan2013). Your formula is also giving me a total result much larger than what it should be:
For example: As you can see in the images below, the result of the average of the recent 6 months is 4.95, not 29.814717 as indicated in your "Last 6" - it's adding all the rates together. I'm trying to figure out a formula that will capture this total recent 6 month average (4.95) and the total 18 month average prior to the recent 6 months (4.63) as distinct numeric fields so I can then write a formula for KPI that will compare the two (6mon) 4.95 and (18mon prior to recent 6) 4.63 - Because the rate for the recent 6 months is greater than the 18mon prior to the recent 6, I would use a KPI indicating no improvement.
Correct me if I'm missing something. Thanks for trying to help me out here! I'm stumped!