Accumulative sum per project per month

[cheryl]

HI friends,

I have a table like this. and I want to calculate the accumulative sum per project, for each month.

What I want to achieve is:

Anyone has any idea how I should write in Script to transform the first table to the second table?

Many thanks,

Cheryl

[JordyWegman]

Hi Cheryl,

Try to use this attached app.

This is my result with your data.

What I did was creating zero values for all possibility and use a RangeSum() function in the front-end. You can optimize this by adding a where not exists() with the concatenation, so you don't get any duplicate rows! Let me know if you need help with this.

Table:
Load * Inline [
Project ID	, Date			, Location	, Duration
P-111		, 02-01-2022	, A			, 1
P-111		, 05-01-2022	, B			, 2
P-111		, 04-01-2022	, A			, 3
P-111		, 05-03-2022	, C			, 1
P-111		, 05-04-2022	, A			, 2
P-111		, 08-05-2022	, B			, 3
P-112		, 02-02-2022	, A			, 1
P-112		, 05-03-2022	, C			, 2
P-112		, 04-03-2022	, C			, 3
P-112		, 05-05-2022	, B			, 1
P-112		, 08-05-2022	, A			, 2
];

Overige:
Load distinct
	[Project ID]
Resident Table;

Left Join( Overige )
Load distinct
	Date
Resident Table;


Left Join( Overige )
Load distinct
	Location
Resident Table;


Left Join( Overige )
Load distinct
	0 as Duration
Resident Table;

Concatenate( Table )
Load
	*
Resident Overige;

Drop table Overige;

Front-end calculation:

RangeSum( 
    above(
	     Sum(Duration)
    ,0,rowNo()
    )
)

Jordy

Climber

[cheryl]

HI Jordy, Thank you very much for the solution!

But do you also know a way that this table can be created in the back-end instead of front-end? Just like the second flat table I showed in the question? This is because I was hoping to directly use the column in the desired tables because there were already a lot of other calculation going on in the front-end.

Thanks in advance!

[JordyWegman · Accepted solution]

Hi Cheryl,

Yes this is also possible, check the script below. I've created a function for the Final table so I don't have to write the code 3 times.

Table:
Load * Inline [
Project ID	, Date			, Location	, Duration
P-111		, 02-01-2022	, A			, 1
P-111		, 05-01-2022	, B			, 2
P-111		, 04-01-2022	, A			, 3
P-111		, 05-03-2022	, C			, 1
P-111		, 05-04-2022	, A			, 2
P-111		, 08-05-2022	, B			, 3
P-112		, 02-02-2022	, A			, 1
P-112		, 05-03-2022	, C			, 2
P-112		, 04-03-2022	, C			, 3
P-112		, 05-05-2022	, B			, 1
P-112		, 08-05-2022	, A			, 2
];

Overige:
Load distinct
	[Project ID],
	Date
Resident Table;


Locations:
Load distinct
	Location as UniqueLocation
Resident Table;

For each i in FieldValueList('UniqueLocation' )
	
    Left Join( Overige )
    Load distinct
		[Project ID],
    	Date,
        Duration as Location_$(i)
    Resident Table
    Where Location = '$(i)'
    ;

Next i

Drop table Locations;
Drop table Table;

set vFunction = 
    IF( RowNo() = 1
    	, Coalesce( $1 , 0 )
        , IF( [Project ID] = Previous( [Project ID] )
        	, Peek( $2 ) + Coalesce( $1 , 0 )
            , Coalesce( $1 , 0 )
		)
	) ;

NoConcatenate
Final:
Load
	[Project ID],
    Date,
    $(vFunction(Location_A, A)) as A,
    $(vFunction(Location_B, B)) as B,
    $(vFunction(Location_C, C)) as C
Resident Overige
Order by 
	[Project ID],
    Date,
;

Drop table Overige;

Jordy

Climber