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)
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] )
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.