Running totals on Pivot table
Rich Taylor
I am trying to develop a deepsee pivot that looks a financial transactions. Particularly an income and expense budget. I can get the view to show the income and expense in rows with columns defined by the year and month of the transaction. I also can get the totals for each time period (each month). However what I want to see also is a running total of the total from each month. So after the first month the answer would be the previous running total plus the sum of income and expenses in the current month. I cannot determine how to achieve this. Any ideas?
Richard S Taylor
Sales Engineer
InterSystems Corporation
Office: 443-340-8614
FAX: 440-815-5805
Rich Taylor
From: Joe Gallant
Sent: Monday, June 22, 2015 9:53 AM
To: Rich Taylor
Cc: intersystems-de...@googlegroups.com; Ussaleseng; DSII
Subject: Re: Running totals on Pivot table
Rich,
In the SAMPLES namespace look at the pivot table:
Pareto Charts/Category Pareto Data
It shows a column that is "Revenue Cumulative Percentage", a calculated member (measure) which is a running total using the MDX function %CELL. Each value is being added the the previous running total, which is in the same column and one row above: %CELL(0.-1)
In this example, the running total is based on another calculated member "Revenue as Percent of Total”, but it could be based on any measure.
The pivot is use in the dashboard:
Pareto Charts/Pareto Chart for Category
which uses a Combo Chart with multiple axes to create a Pareto Chart.
Joe G
<image001.jpg>