Reporting Running Totals for Trailing 12 Months

[kw]

Hello all,

I'm struggling trying to report running totals for the previous 12 months on a monthly basis. 

I have a set of sales data starting 1/1/21 through present. I'd like to create a crosstab in a report as well as a column chart in a dashboard that shows each month and the total of all sales for the preceding 11 months and the sales of the month being reported.

So, starting Jan 2022 I'd like to show the total of all sales of each month for the 11 preceding months ( Feb 2021 to Dec 2021 ) + Jan 2022.

Here's a sample of my data (apologies in advance for the formatting, I don't know how to make it a table here in the text of the post) and a word doc attached with the data from excel.

Any thoughts or insight how to achieve this in Cognos 11.2.3 ?



Row    month            sales            running total    excel formula
2    Jan 2021    $500,000       
3    Feb 2021    $400,000       
4    Mar 2021    $300,000       
5    Apr 2021    $1,000,000       
6    May 2021    $900,000       
7    Jun 2021    $700,000       
8    Jul 2021    $500,000       
9    Aug 2021    $250,000       
10    Sep 2021    $1,500,000       
11    Oct 2021    $675,000       
12    Nov 2021    $415,000       
13    Dec 2021    $825,000      $7,965,000      SUM(C2:C13)
14    Jan 2022    $825,000      $8,290,000      SUM(C3:C14)
15    Feb 2022    $415,000      $8,305,000      SUM(C4:C15)
16    Mar 2022    $675,000      $8,680,000      SUM(C5:C16)
17    Apr 2022    $1,500,000      $9,180,000      SUM(C6:C17)
18    May 2022    $250,000      $8,530,000      SUM(C7:C18)
19    Jun 2022    $500,000      $8,330,000      SUM(C8:C19)
20    Jul 2022    $700,000      $8,530,000      SUM(C9:C20)
21    Aug 2022    $900,000      $9,180,000      SUM(C10:C21)
22    Sep 2022    $1,000,000      $8,680,000      SUM(C11:C22)
23    Oct 2022    $300,000      $8,305,000      SUM(C12:C23)
24    Nov 2022    $400,000      $8,290,000      SUM(C13:C24)
25    Dec 2022    $500,000      $7,965,000      SUM(C14:C25)
26    Jan 2023    $500,000      $7,640,000      SUM(C15:C26)
27    Feb 2023    $400,000      $7,625,000      SUM(C16:C27)
28    Mar 2023    $300,000      $7,250,000      SUM(C17:C28)
29    Apr 2023    $1,000,000      $6,750,000      SUM(C18:C29)
30    May 2023    $900,000      $7,400,000      SUM(C19:C30)
31    Jun 2023    $700,000      $7,600,000      SUM(C20:C31)
32    Jul 2023    $500,000      $7,400,000      SUM(C21:C32)
33    Aug 2023    $250,000      $6,750,000      SUM(C22:C33)
34    Sep 2023    $1,500,000      $7,250,000      SUM(C23:C34)
35    Oct 2023    $675,000      $7,625,000      SUM(C24:C35)
36    Nov 2023    $415,000      $7,640,000      SUM(C25:C36)
37    Dec 2023    $825,000      $7,965,000      SUM(C26:C37)
38    Jan 2024    $825,000      $8,290,000      SUM(C27:C38)
39    Feb 2024    $415,000      $8,305,000      SUM(C28:C39)
40    Mar 2024    $675,000      $8,680,000      SUM(C29:C40)
41    Apr 2024    $1,500,000      $9,180,000      SUM(C30:C41)
42    May 2024    $250,000      $8,530,000      SUM(C31:C42)
43    Jun 2024    $500,000      $8,330,000      SUM(C32:C43)
44    Jul 2024    $700,000      $8,530,000      SUM(C33:C44)
45    Aug 2024    $900,000      $9,180,000      SUM(C34:C45)
46    Sep 2024    $1,000,000      $8,680,000      SUM(C35:C46)
47    Oct 2024    $300,000      $8,305,000      SUM(C36:C47)
48    Nov 2024    $400,000      $8,290,000      SUM(C37:C48)
49    Dec 2024    $500,000      $7,965,000      SUM(C38:C49)

[kw]

Not sure if anyone else has seen this or is looking for a solution but I discovered that the following function is how to do this.

Moving-total ( [Sales] , 12 for [Month] )

[kw]

Additional details about the reply above:

Also need to presort the [Month] field as ascending in the properties within the query.

The only way I've found this to work is with a report. I cannot get this to work properly within a dashboard.