
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to Calculate Backlog Over the Period
Hi
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
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Mark the solution as accepted that solved your problem and if you found it useful, press the like button! Check out my YouTube Channel | Follow me on LinkedIn

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Mark the solution as accepted that solved your problem and if you found it useful, press the like button! Check out my YouTube Channel | Follow me on LinkedIn

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
