Hello Tushar, That works just fine when we have full data, thank you. Unfortunately there is a case, when we can have a gap and no data for one of the months. In that case, this 'missing' month should be also taken into account while rolling. Is this possible?
I'm working on similar case in my company.
Unfortunately this isn't work. Look please, in my case (SUM) it should be.
Month Value SUM(Value Rolled 3 Months)
1 no have data for 3 months February 2 no have data for 3 months March 3 6 (1 + 2 + 3) April 4 9 (2 + 3 + 4) May 5 12 (3 + 4 + 5) June 6 15 (4 + 5 + 6) July 7 18 (5 + 6 + 7) August 8 21 (6 + 7 + 8) October 10 18 (8 + 0 + 10); 0 - missing value for September December 12 22 (10 + 0 + 12); 0 - missing value for NovemberI'm working on similar case in my company
rolled months.twbx 8.2 KB
Hi Piotr and Paul,
If you've missing values then it is very difficult to get the desired result, as Tableau will only work on the available data. So, to make it work you will have to create a separate table wherein you can add all possible date values. You can create this in your db,or in excel. Join this table with your existing tables using left or right join. With this in mind, I created one master that includes all the date values for this year.
Now, I connect to this file along with the actual file (Date_val) and performed cross-join.
Create a calculated field to get running sum/avg.
If you want, you can hide the missing values.
And the final result.
Let me know if this helps.
You can refer to the below thread if you need more info.
Rolling missing months.twbx 33.0 KB
Thank You Tushar. It's work