I'm trying to do a trend chart that shows a rolling 12 month sum of the current and previous 11 months over time. I have a filter set to filter the view and not the data (lookup(min([Month]),0) >= [Start Month] AND lookup(min([Month]),0) <= [End Month]) and the calculation I have after following the article on data densification seems to work for months missing in the middle of the time frame I'm looking at. However, is there a way to fill in the empty columns if there is no data at the end of beginning of the view?
I'm trying to show the rolling 12 months for company Bolten Cats and Tomcris dogs, and the view I'm interested in is the 12 months between November 2015 and October 2016. The data point for November 15 is based on dates from 12/1/14 - 11/30/15, and for Oct. 2016 it is for data between 11/1/15 and 10/31/16. The Lookup New filter filters the views correctly without filtering the underlying data. I have a calculated field for sort state filed that shows the rolling amount for the last 12 months, which is used to sort the companies correctly. I have analysis->Table Options-> Show Empty columns checked, and then Analysis->Stack Marks->ON. I have one month missing for company Tomcris Dogs (July 2016), and the graphs seems to be correct as well as the label. I have numerous months missing for company Bolten Cats, and the label seems to be correct; however, the graph ends at August 2016 instead of going to October 2016. Any help to get the line extended to October 2016 and starting at November 2015 would be helpful!
Rolling 12 Missing Months.twbx 17.9 KB