Tableau 12-Rolling-Month Calculation for Line Graph

1
votes

enter image description hereI currently have a Tableau Dashboard that grabs a parameter date that the user can enter and reflects a line graph (the line graph displays a 12 month look back period from what the user entered) of percentages (basically the sum of records per category / sum of ALL record)

The tricky part here is that the user wants to see EACH month within this look back period month as it's OWN 12-month look back period.

So currently Tableau is displaying all the 12-months from Jan 19 to Jan 18. And obviously it counts all the records given WITHIN that month (not the whole 12-month look back period).

But what I'd like it to do is for Jan 19 - it would show ALL records from (jan-18 to jan-19) and then in Dec 18 bucket it would show all records from (dec 18 to dec 17) etc.

Just for further clarification - as you can see from the pic - Jan 19 has a < .2% Percentage for JUST the month of that data - BUT - what we want to see here is the actual full count of ALL records going back 12 months from this date (which would actually make it around 3% - not .2%).

1
sqlsql-servertableau-api
It's not entirely clear what your data looks like, what aggregation method you're using and what your desired output is. – Chris
Ok requirement looks like every point on the line graph should reflect one year data, this means in graph from Jan to Dec you will end up having data for 2 years, is this correct? – Siva

1 Answers

3
votes

Here is a formula for the rolling 12 months sum:

window_sum(sum([your_measure_name]), -11, 0)

window_sum returns the sum of the expression within the window defined by offsets from the current row. In your case we want 11 previous records plus the current one.

enter image description here