3 Replies Latest reply on Mar 26, 2018 4:51 AM by Zhouyi Zhang

    Creating a Dynamic Rolling Ratio Chart

    Kathryn Nelson

      Hi there,


      I have attached a file with some dummy data that resembles what I am working with.

      I am trying to get a chart- with a rolling 12 month ratio across months.


      I have two main variables: Sales and Returns. What I am looking for is the return ratio: Returns/Sales.

      Except: I want it to include the prior 12 months on both sides of the equation. For example:


      February 2018 point: sum of returns from March 2017-Feb 2018/sum of sales form March 2017-Feb 2018

      January 2018 point: sum of returns from Feb 2017-Jan 2018/sum of sales form Feb 2017-Jan 2018

      December 2017 point: sum of returns from Jan 2017-Dec 2017/sum of sales form Jan 2017-Dec 2017

      November 2017 point:sum of returns from Dec 2016-Nov 2018/sum of sales form Dec 2016-Nov 2017

      and so on until I get to 12 months before the min date of my set.


      I also want this to be dynamic as I add new data (new months).


      I'd also like to be able to filter by a couple of dimensions: country, family, and manufacturer.


      Any help?