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?