EducationSoftwareStrategy.com
StrategyCommunity

Knowledge Base

Product

Community

Knowledge Base

TopicsBrowse ArticlesDeveloper Zone

Product

Download SoftwareProduct DocumentationSecurity Hub

Education

Tutorial VideosSolution GalleryEducation courses

Community

GuidelinesGrandmastersEvents
x_social-icon_white.svglinkedin_social-icon_white.svg
Strategy logoCommunity

© Strategy Inc. All Rights Reserved.

LegalTerms of UsePrivacy Policy
  1. Home
  2. Topics

KB8679: How to use the MovingSum Function in a metric calculation in MicroStrategy Developer 10.X


Stefan Zepeda

Salesforce Solutions Architect • Strategy


SUMMARY: 
The Moving Sum function returns a cumulative total of values based on a specified window size. The window size is a parameter which tells the function how many of the values should be included in the calculation.
For example, in the following formula - MovingSum(Revenue, 3) - only the previous three values are to be included in the calculation. Consider the following table:

X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM  1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550

X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM  1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550

X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM  1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550

X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM  1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550

X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM  1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550

X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM  1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550

X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM  1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550

X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM  1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550

X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM  1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550

X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM  1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550

X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM  1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550

X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM  1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550

X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM  1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550

X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM  1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550

X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM  1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550

X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM  1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550

X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM  1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550

X
Service NameDefault PortApache Kafka 9092Apache Tomcat 8.0 Tomcat8 8080Apache Tomcat 8.0 usherGW 9501Apache Tomcat 8.0 usherIDM  1443 & 2443Apache ZooKeeper 2181Apache2.4 443Memcached 11211MongoDB 27017MySQL 3306Redis 6379Strategy Collaboration/Realtime Service 3000Strategy Intelligence Server 34952Strategy PDFExport Service 20100Strategy REST Server 7070Strategy Usher Analytics Log Consumer Strategy Distribution Manager Strategy Enterprise Manager Data Loader Strategy Execution Engine Strategy Health Agent Strategy Listener Strategy Logging Client Strategy Logging Consumer Strategy Logging Server Strategy Master Health Agent Strategy MongoDB SQL Engine Strategy NC PDF Formatter Strategy SMTP Service Strategy System Monitor DayRevenueMovingRevenue1/1/20001501501/2/20001002501/3/20002004501/4/20001004001/5/2000250550

In the table above, the MovingSum value, called MovingRevenue, for the date 1/3/2000 includes in its calculation the revenue values for that date and the preceding two dates. This would mean that the MovingRevenue value is the sum of the revenue values for the dates 1/1/2000, 1/2/2000, and 1/3/2000. This is because the window size parameter is set to three. Also notice that the MovingSum values for the dates 1/1/2000 and 1/2/2000 do not include data before 1/1/2000 in its calculation. This is because the current report does not include those elements in the filter.
 
This following metric is defined as MovingSum(Revenue, 3), where the MovingSum parameters are all set to the default (no break-by or sort-by is defined):
 

ka02R000000kchXQAQ_0EM440000002G5r.gif

 
The following is a simple report with the same metric.
 

ka02R000000kchXQAQ_0EM440000002G6I.gif

 
Since the sort is not specified in the metric, it will take on the default sort, which is ascending by the values of metric on which the MovingSum is being performed. For example, the metric is defined to be a MovingSum (Revenue) and by default, it will sort by the revenue values. If the report is sorted on the revenue, observe that the MovingSum metric values are correctly calculated, as shown below:
 

ka02R000000kchXQAQ_0EM440000002G6E.gif

 
If the Moving Sum is to be sorted by an attribute on the report, it is necessary to specify this in the 'Sort By' parameters of the MovingSum function. To sort by an attribute, highlight 'MovingSum' in the metric editor expression window. Right-click and then choose 'MovingSum parameters'.
 

ka02R000000kchXQAQ_0EM440000002G5s.gif

 
A report with this metric follows:
 

ka02R000000kchXQAQ_0EM440000002G6N.gif

 
Notice that the report is now sorted ascending by the day attribute. This forces the calculation of the MovingSum to no longer be based on the preceding revenue values but to now be based on the preceeding dates.
 
Break By:
The following is an example of how the break-by parameter behaves. On the sample report, the 'Month' attribute has been added. Notice in the report that the MovingSum continues to calculate on the days without taking into consideration the change in Month:
 

ka02R000000kchXQAQ_0EM440000002G68.gif

 
Next, modify the metric to include a break-by on Month so the MovingSum calculation will start over every month.
 

ka02R000000kchXQAQ_0EM440000002G6A.gif

 
Lastly, using the modified metric in a report, observe that the calculation is starting over in Feb-00.
 

ka02R000000kchXQAQ_0EM440000002G6C.gif

 


Comment

0 comments

Details

Knowledge Article

Published:

April 4, 2017

Last Updated:

April 4, 2017