nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

creating 12 month rolling total

lgudait

Hello,

I'm trying to create a report where there's a field that shows totals for the previous 12 months.

This is my select statement:

select YEAR(CLASSICMODELS.ORDERS.ORDERDATE) AS "YEAR", MONTH(CLASSICMODELS.ORDERS.ORDERDATE) "MONTH"
, sum(CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED*CLASSICMODELS.ORDERDETAILS.PRICEEACH) as Sales
from CLASSICMODELS.ORDERS,CLASSICMODELS.ORDERDETAILS
where CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS.ORDERDETAILS.ORDERNUMBER
group by YEAR(CLASSICMODELS.ORDERS.ORDERDATE), MONTH(CLASSICMODELS.ORDERS.ORDERDATE)

I also included a computed column to give me the running count, and an aggregation element for the running sum, which give me the following results:

YEAR | MONTH | SALES |RUNNING_COUNT | RUNNING_SUM
2003 | 1 | $116,692.77 | 1 | $116,692.77
2003 | 2 | $128,403.64 | 2 | $245,096.41
2003 | 3 | $160,517.14 | 3 | $405,613.55
2003 | 4 | $185,848.59 | 4 | $591,462.14
2003 | 5 | $179,435.55 | 5 | $770,897.69
2003 | 6 | $150,470.77 | 6 | $921,368.46
2003 | 7 | $201,940.36 | 7 | $1,123,308.82
2003 | 8 | $178,257.11 | 8 | $1,301,565.93
2003 | 9 | $236,697.85 | 9 | $1,538,263.78
2003 | 10 | $514,336.21 | 10 | $2,052,599.99
2003 | 11 | $988,025.15 | 11 | $3,040,625.14
2003 | 12 | $276,723.25 | 12 | $3,317,348.39
2004 | 1 | $292,385.21 | 13 | $3,493,040.83
2004 | 2 | $289,502.84 | 14 | $3,899,236.44
2004 | 3 | $217,691.26 | 15 | $4,116,927.70

What I'm actually looking for, though, is for the running_sum= sum(sales) where the running count is between the (current record - 12) and the current record. The output I'm looking for is below:

YEAR | MONTH | SALES |RUNNING_COUNT | RUNNING_SUM
2003 | 1 | $116,692.77 | 1 | $116,692.77
2003 | 2 | $128,403.64 | 2 | $245,096.41
2003 | 3 | $160,517.14 | 3 | $405,613.55
2003 | 4 | $185,848.59 | 4 | $591,462.14
2003 | 5 | $179,435.55 | 5 | $770,897.69
2003 | 6 | $150,470.77 | 6 | $921,368.46
2003 | 7 | $201,940.36 | 7 | $1,123,308.82
2003 | 8 | $178,257.11 | 8 | $1,301,565.93
2003 | 9 | $236,697.85 | 9 | $1,538,263.78
2003 | 10 | $514,336.21 | 10 | $2,052,599.99
2003 | 11 | $988,025.15 | 11 | $3,040,625.14
2003 | 12 | $276,723.25 | 12 | $3,317,348.39
2004 | 1 | $292,385.21 | 13 | $3,493,040.83
2004 | 2 | $289,502.84 | 14 | $3,654,140.03
2004 | 3 | $217,691.26 | 15 | $3,711,314.15

Any help on how I can build this is appreciated.

Thanks!

Find more posts tagged with

Comments

mwilliams

What's your BIRT version?

mwilliams

Here's a solution. It's probably not the only way you could go, but it works. In the beforeOpen of my dataSet, I create an empty array and a couple variables to track my rows and total. In the computed column in the dataSet, I use the array to keep the last 12 values, popping the 13th one off and moving the others up in line so that the first value in the array is always the one I need to subtract from my current total. The row count is used to keep track of where I'm placing the first 12 values and the sum is where I keep the sum. Take a look. Let me know if you have any questions.

This report was made in BIRT 3.7.0.

lgudait

Thanks for the quick reply, Michael.

The report worked... until line 24 (see 2004-12). Results are below, with the actual rolling total calculated in excel as a QC measure. Any other suggestions?

BTW, I'm using BIRT 2.6.2

DATE | SALES | SumOfLast12 | excel_SumOfLast12 | DIFF
2003-1 | 116692.77 | 116692.77 | 116692.77 | 0
2003-2 | 128403.64 | 245096.41 | 245096.41 | 0
2003-3 | 160517.14 | 405613.55 | 405613.55 | 0
2003-4 | 185848.59 | 591462.14 | 591462.14 | 0
2003-5 | 179435.55 | 770897.69 | 770897.69 | 0
2003-6 | 150470.77 | 921368.46 | 921368.46 | 0
2003-7 | 201940.36 | 1123308.82 | 1123308.82 | 0
2003-8 | 178257.11 | 1301565.93 | 1301565.93 | 0
2003-9 | 236697.85 | 1538263.78 | 1538263.78 | 0
2003-10 | 514336.21 | 2052599.99 | 2052599.99 | 0
2003-11 | 988025.15 | 3040625.14 | 3040625.14 | 0
2003-12 | 276723.25 | 3317348.39 | 3317348.39 | 0
2004-1 | 292385.21 | 3493040.83 | 3493040.83 | 0
2004-2 | 289502.84 | 3654140.03 | 3654140.03 | 0
2004-3 | 217691.26 | 3711314.15 | 3711314.15 | 0
2004-4 | 187575.77 | 3713041.33 | 3713041.33 | 0
2004-5 | 248325.3 | 3781931.08 | 3781931.08 | 0
2004-6 | 343370.74 | 3974831.05 | 3974831.05 | 0
2004-7 | 325563.49 | 4098454.18 | 4098454.18 | 0
2004-8 | 419327.09 | 4339524.16 | 4339524.16 | 0
2004-9 | 283799.8 | 4386626.11 | 4386626.11 | 0
2004-10 | 500233.86 | 4372523.76 | 4372523.76 | 0
2004-11 | 979291.98 | 4363790.59 | 4363790.59 | 0
2004-12 | 428838.17 | 4500243.55 | 4515905.51 | 15661.96
2005-1 | 307737.02 | 4518477.73 | 4531257.32 | 12779.59
2005-2 | 317192.17 | 4617978.64 | 4558946.65 | -59031.99
2005-3 | 359711.96 | 4790114.83 | 4700967.35 | -89147.48
2005-4 | 344820.62 | 4886610.15 | 4858212.2 | -28397.95
2005-5 | 441474.94 | 4984714.35 | 5051361.84 | 66647.49

mwilliams

Very odd. I see what it's doing, but I'm not sure why it's doing that. It's like the array shift happens before the subtraction at that point, so you're actually subtracting the value that ends up in last12[0] after the shift. Let me work on it a bit and see if I can get around this.

mwilliams

By putting all the values in the array and no shift and just counting back 12 spots, it works. Take a look at the second computed column. I get the same numbers all the way down now as your excel spreadsheet numbers.

lgudait

I was able to translate this into my report - thanks so much!

mwilliams

You're welcome. If I come up with a better solution, I'll share it with you.

Let us know whenever you have questions!

lgudait

This has been working fantastically for me. I've used it to create 12 month rolling sums, 12 month moving averages, and 12 month moving percentages.

One thing to note - you need to make sure that each month is populated in the data set, otherwise going back 12 records may be more than 12 months. I accounted for this by creating a temp table in the sql with the distinct list of months that I'm looking for, then outer join this to a second temp table that has my data.

Thanks again!

mwilliams

Awesome! Thanks for the update! And, again, you're very welcome!

lgudait

Hello again!

Is there any way to modify the JavaScript so that the size of the array is equivalent to the month of the data row? I need to calculate Year-to-date running totals. Unfortunately I have no working knowledge of JavaScript, so I can't play with the code like I could with SQL.

mwilliams

You need a running total now? For the month? Or for the year? I'm not sure I'm getting it exactly. Can you explain more?

lgudait

I need a YTD running total. I calculated what I'm looking for in excel off of the classic models data. The running sum for the month listed should equal the sum for all the months to date in that year. I assume this can be achieved by somehow dynamically setting the array size to the number value for the month, but I don't know how to implement. I need a javascript class like yesterday.

SQL script to calculate sales by month:

select YEAR(CLASSICMODELS.ORDERS.ORDERDATE) AS "YEAR", MONTH(CLASSICMODELS.ORDERS.ORDERDATE) "MONTH",
sum(CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED * CLASSICMODELS.ORDERDETAILS.PRICEEACH) as Sales
from CLASSICMODELS.ORDERS,CLASSICMODELS.ORDERDETAILS
where CLASSICMODELS.ORDERS.ORDERNUMBER = CLASSICMODELS.ORDERDETAILS.ORDERNUMBER
group by YEAR(CLASSICMODELS.ORDERS.ORDERDATE), MONTH(CLASSICMODELS.ORDERS.ORDERDATE)

Expected results out of classic models:

YEAR MONTH SALES YTD Running Sum
2003 1 116692.77 116692.77
2003 2 128403.64 245096.41
2003 3 160517.14 405613.55
2003 4 185848.59 591462.14
2003 5 179435.55 770897.69
2003 6 150470.77 921368.46
2003 7 201940.36 1123308.82
2003 8 178257.11 1301565.93
2003 9 236697.85 1538263.78
2003 10 514336.21 2052599.99
2003 11 988025.15 3040625.14
2003 12 276723.25 3317348.39
2004 1 292385.21 292385.21
2004 2 289502.84 581888.05
2004 3 217691.26 799579.31
2004 4 187575.77 987155.08
2004 5 248325.3 1235480.38
2004 6 343370.74 1578851.12
2004 7 325563.49 1904414.61
2004 8 419327.09 2323741.7
2004 9 283799.8 2607541.5
2004 10 500233.86 3107775.36
2004 11 979291.98 4087067.34
2004 12 428838.17 4515905.51
2005 1 307737.02 307737.02
2005 2 317192.17 624929.19
2005 3 359711.96 984641.15
2005 4 344820.62 1329461.77
2005 5 441474.94 1770936.71

mwilliams

Ok. This one is much easier. If you group your table by year, then delete the sales field and replace it with an aggregation with the RUNNINGSUM function, making sure to select the "group" radio button, you should get exactly this in your table with no need for any custom javascript.

lgudait

<blockquote class='ipsBlockquote' data-author="'mwilliams'" data-cid="92149" data-time="1324071277" data-date="16 December 2011 - 02:34 PM"><p>
Ok. This one is much easier. If you group your table by year, then delete the sales field and replace it with an aggregation with the RUNNINGSUM function, making sure to select the "group" radio button, you should get exactly this in your table with no need for any custom javascript.<br /></p></blockquote>
<br />
Fantastic! Thanks!