Rolling Average Calculation

Report Inappropriate Content · ‎2016-10-19

Hi,

I have build a QVD with 2 years of data in date lavel and now wanted to build a logic like this
The formula should be Sum(Txn current date)/Average(last 14 days excluding current date) – 1

Example:
10/3/2016 Txn should be compared to average of Txn from (10/2/2016 – 09/19/2016)
10/2/2016 Txn should be compared to average of Txn from (10/1/2016 – 09/18/2016)
10/1/2016 Txn should be compared to average of Txn from (09/30/2016 – 09/17/2016)

Sample Data:

Required output:

Result Set (will always display latest 15 days)
Date ->	10/15/2016	10/14/2016	10/13/2016	10/12/2016	10/11/2016	10/10/2016	10/9/2016	10/8/2016	10/7/2016	10/6/2016	10/5/2016	10/4/2016	10/3/2016	10/2/2016	10/1/2016
Partner A	93.0%	93.1%	93.2%	93.2%	93.3%	93.3%	93.4%	93.4%	93.5%	93.6%	93.6%	93.7%	93.7%	93.8%	93.8%
Partner B	97.4%	97.4%	97.4%	97.4%	97.4%	97.4%	97.4%	97.4%	97.4%	97.4%	97.4%	97.5%	97.5%	97.5%	97.5%
Partner C	87.0%	87.2%	87.4%	87.6%	87.8%	88.0%	88.2%	88.4%	88.5%	88.7%	88.9%	89.1%	89.2%	89.4%	89.5%
Partner D	95.9%	96.0%	96.0%	96.0%	96.0%	96.0%	96.1%	96.1%	96.1%	96.1%	96.1%	96.2%	96.2%	96.2%	96.2%

Related Topics