Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for
Did you mean:
Contributor III

## Accumulative sum per project per month

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

Labels (4)

• ### Variables

1 Solution

Accepted Solutions
Partner - Master

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:
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:
[Project ID],
Date
Resident Table;

Locations:
Location as UniqueLocation
Resident Table;

For each i in FieldValueList('UniqueLocation' )

Left Join( Overige )
[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:
[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

Work smarter, not harder
3 Replies
Partner - Master

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:
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:
[Project ID]
Resident Table;

Left Join( Overige )
Date
Resident Table;

Left Join( Overige )
Location
Resident Table;

Left Join( Overige )
0 as Duration
Resident Table;

Concatenate( Table )
*
Resident Overige;

Drop table Overige;``````

Front-end calculation:

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

Jordy

Climber

Work smarter, not harder
Contributor III
Author

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.

Partner - Master

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:
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:
[Project ID],
Date
Resident Table;

Locations:
Location as UniqueLocation
Resident Table;

For each i in FieldValueList('UniqueLocation' )

Left Join( Overige )
[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:
[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

Work smarter, not harder
Tags
Community Browser