How to Calculate Backlog Over the Period

[shahzad]

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 

[JandreKillianRIC · Accepted solution]

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

[shahzad]

Thanks @JandreKillianRIC 

This is what i was looking for.