Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
shahzad
Contributor II

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 

Labels (3)
1 Solution

Accepted Solutions
JandreKillianRIC
Partner Ambassador

Hi @shahzad 

Here is my result table 

JandreKillianRIC_0-1742475782696.png

 

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

View solution in original post

2 Replies
JandreKillianRIC
Partner Ambassador

Hi @shahzad 

Here is my result table 

JandreKillianRIC_0-1742475782696.png

 

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

shahzad
Contributor II
Author

Thanks @JandreKillianRIC 

This is what i was looking for.