topic Re: How to Calculate Backlog Over the Period in App Development

How to Calculate Backlog Over the Period

shahzad — Thu, 20 Mar 2025 10:35:11 GMT

I am calculating Backlog over the period. I have attached the sample data.

ContractValue is a fixed value for each ID. While Sales is a transaction data for each date.

I want to calculate Backlog. You can say its a kind of balance.

Formula For Backlog is (ContractValue - Sales)

Backlog is like RangeSum in subtraction mode.

I want to sort by Date Desc and Id as well. Looking to do in script level.

Thanks

Best Regards

Shahzad

Re: How to Calculate Backlog Over the Period

JandreKillianRIC — Thu, 20 Mar 2025 13:03:33 GMT

Hi @shahzad

Here is my result table

Here is the script.

SampleDataTmp: Load *, Date(Date) as FormattedDate; Load * Inline [ ID,Date,Contract Value,Sales 101,45658,1000,300 101,45689,1000,200 101,45736,1000,100 103,45713,3000,900 102,45672,2500,1000 101,45731,1000,150 102,45708,2500,1000 103,45704,3000,600 103,45736,3000,1500 ]; SampleData: Load *, [Contract Value] - RollingSales as Backlog; Load *, If(ID = Previous(ID), Sales + Peek('RollingSales'), Sales) as RollingSales Resident SampleDataTmp Order By ID, FormattedDate; Drop Table SampleDataTmp;

Regards Jandre

Re: How to Calculate Backlog Over the Period

shahzad — Fri, 21 Mar 2025 12:09:38 GMT

Thanks @JandreKillianRIC

This is what i was looking for.