Rolling Sparklines

[asajnani]

Hi there,

I have sparklines set up to feed off a pivot table. What I need to figure out is how to make it a rolling sparkline. For example, if the current month is March 2015, I want the sparkline to show January, Febuary and March 2015. If June 2015, I'd like the sparkline to display April, May and June 2015.

How do I set this up to be automated? I've included the spreadsheet.


Thanks so much,
A

 

[Chihiro]

There are few ways that come to mind that can accomplish what you are looking for.

1) Calculated table (with formula), with Index, Match or SUMPRODUCT to pull the data for respective month. This can replace second set of Pivots from Row 21~.

2) You can tie slicer for first set of Pivots to filter second set of Pivots using macro. See links below on how to access slicer through VBA.
http://www.jkp-ads.com/articles/slicers00.asp
https://paultebraak.wordpress.com/2012/02/24/accessing-the-slicer-through-vba/

3) Using method outlined in link, add another slicer for second set of pivots. You can leave it as is, or tie it back to the slicer for first set of Pivots via Macro.
http://www.excelguru.ca/content.php?263-Retrieving-Selections-From-A-PivotTable-Slicer

 

[asajnani]

Great, thank you!