Showing cumulative in a chart

[Anonymous]

Hi

I have a log of disputed invoices and I want to show the cumulative of unresolved items.  If an item has been resolved I want it to be included in all the weeks up until it was resolved.  My data is similar to this:

Number	Date Entered	Week Entered	Amount	Date Resolved	Week Resolved
1	10/05/2017	19	609.06	25/06/2017	26
2	13/05/2017	19	992.96	27/05/2017	21
3	14/05/2017	20	812.94	20/05/2017	20
4	19/05/2017	20	577.54
5	23/05/2017	21	78.82	22/06/2017
6	25/05/2017	21	306.54
7	25/05/2017	21	982.49
8	26/05/2017	21	170.47
9	26/05/2017	21	707.13	30/05/2017	22
10	30/05/2017	22	658.47	05/06/2017	23
11	31/05/2017	22	689.73
12	31/05/2017	22	883.85
13	01/06/2017	22	856.51	29/06/2017	26
14	03/06/2017	22	296.76
15	03/06/2017	22	676.41	27/06/2017	26
16	04/06/2017	23	442.61
17	07/06/2017	23	253.07
18	08/06/2017	23	184.38
19	11/06/2017	24	430.78	11/06/2017	24
20	12/06/2017	24	921.79	25/06/2017	26
21	12/06/2017	24	79.73	18/06/2017	25
22	13/06/2017	24	276.19	26/06/2017	26
23	14/06/2017	24	136.31	23/06/2017	25
24	15/06/2017	24	437.31
25	21/06/2017	25	989.07	24/06/2017	25
26	24/06/2017	25	400.28
27	24/06/2017	25	63.20
28	26/06/2017	26	351.27	28/06/2017	26
29	30/06/2017	26	386.38	04/07/2017	27
30	04/07/2017	27	218.02

And I want to achieve this:

Week	Cumulative Unresolved
19	1,602.02
20	2,179.56
21	3,432.05
22	6,786.65
23	7,008.24
24	8,859.57
25	9,107.01
26	6,153.43
27	5,985.07

For example week 24 is all the values up to and including week 24 less any resolved values resolved in week 24 and before (item 20 would be included in the cumulative for week 24 and 25 but not in 26)

Please help

Thanks

A

[swuehl · Accepted solution]

You can create records for all dates / weeks between enter date and resolved date (for unresolved, you can use Today() as upper limit), just like shown in

Creating Reference Dates for Intervals

[Anil_Babu_Samineni]

How you are getting 3,432.05 for 21

I am getting like below, I don't think how you are getting. Will you explain little more?

[effinty2112]

Hi Adrian,

This is best achieved with a little bit of scripting:

Add this data island to your model, I've called the table your data comes from Data.

Accumulate:

LOAD

[Week Entered] as Week,

Sum(Amount) as Entered

Resident Data

Group by [Week Entered];

Left Join (Accumulate)

LOAD

[Week Resolved] as Week,

Sum(Amount) as Resolved

Resident Data

Group by [Week Resolved];

Now we can get this straight table:

Week	Cumulative Unresolved
19	£1602.02
20	£2179.56
21	£3432.05
22	£6786.65
23	£7008.24
24	£8859.57
25	£9107.01
26	£6153.43
27	£5985.07

Cumulative Unresolved = Sum(Entered)-Sum(Resolved)

or if you prefer

Cumulative Unresolved = RangeSum(Entered,-Resolved)

It goes without saying but I'll say it anyway: set accumulation on the expression to Full Accumulation.

Cheers

Andrew

[Anonymous]

Once I changed it a bit to fit my data, it worked perfectly.  Such a simple solution

Many thanks