Subtract amount from previous amount in script editor

[klikgevoel]

Hi all,

Got a case where I want to track the flow of sales from customers within Qlik. The main idea is to subtract the first sale from the current budget, and subtract the second sale from the budget that remains after the first sale, and subtract the third sale from the budget that remains after (first sale + second sale), etc.

Problem is, how to deal with this in the script editor...

Some sample data:

Customer:

Load * Inline [

CustomerId, StartingAmount

1, 250

2, 500

3, 750

];

Sales:

Load * Inline [

CustomerId, SalesId, SalesAmount

1, 01, 50

1, 02, 150

2, 03, 250

3, 04, 100

3, 05, 50

3, 06, 100

];

Final result should look like this:

CustomerWallet

CustomerId	TransactionId	RemainingBudget
1	01	200
1	02	50
2	03	250
3	04	650
3	05	600
3	06	500

Help is much appreciated!

[ArnadoSandoval]

Hi @klikgevoel 

I found the information at this thread Select first row of a grouped dimension in load script very helpful, the script below applies its logic to your problem:

NoConcatenate

Customer:
Load * Inline [
CustomerId, StartingAmount
1, 250
2, 500
3, 750
];

NoConcatenate

Sales:
Load * Inline [
CustomerId, SalesId, SalesAmount
1, 01, 50
1, 02, 150
2, 03, 250
3, 04, 100
3, 05, 50
3, 06, 100
];

NoConcatenate

FirstSales:
Load
     CustomerId As Customer_Id,
     CustomerId &'|' As Key,
     SalesAmount
Resident Sales
Where Not Exists(Key, CustomerId &'|')
Order By CustomerId;

Map_FirstSales:
Mapping Load
    Customer_Id,
    SalesAmount
Resident FirstSales;

NoConcatenate

// Apply first sales to customers StartingAmount
New_Customer:
Load CustomerId as Customer_Id,
     ApplyMap('Map_FirstSales', CustomerId,0) as First_Sales,
     StartingAmount - Num(ApplyMap('Map_FirstSales', CustomerId,0)) As New_StartingAmount
Resident Customer;

Drop Table FirstSales;

Hope this helps,

[Kannan_Rajaraman · Accepted solution]

I believe the below code is what you are looking for:

CustomerSales:

Load * Inline [

CustomerId, StartingAmount

1, 250

2, 500

3, 750

];

join

Load * Inline [

CustomerId, SalesId, SalesAmount

1, 01, 50

1, 02, 150

2, 03, 250

3, 04, 100

3, 05, 50

3, 06, 100

];

NoConcatenate

FinalSales:

load CustomerId, SalesId, SalesAmount, StartingAmount,
if(peek(CustomerId,-1) <> CustomerId, StartingAmount - SalesAmount, peek(RemainingBudget) - SalesAmount) as RemainingBudget
Resident
CustomerSales
order by CustomerId, SalesId;

drop table CustomerSales;

Thanks,

Kannan

[klikgevoel]

@Kannan_Rajaraman  this is an elegant solution that works!

EDIT: Managed to get the solution. Final code looks like this with a rolling budget.

TmpSales:

load CustomerId, SalesId, SalesAmount, StartingAmount,
if(peek(CustomerId,-1) <> CustomerId, StartingAmount - SalesAmount, peek(RemainingBudget) - SalesAmount) as RemainingBudget
Resident
CustomerSales
order by CustomerId, SalesId;

drop table CustomerSales;

FinalSales:
Load
*,
If(CustomerId = Previous(CustomerId), If(IsNull(SalesAmount), Peek('StartingAmount'),Previous(RemainingBudget)), StartingAmount) as CurrentBudget
Resident TmpSales
Order by CustomerId, SalesId;

Drop Table TmpSales;

//

Is it also possible to come up with another variable that shows the current amount of the previous remaining budget?

Example,

With the current solution provided it now looks like this:

CustomerID	SalesID	StartingAmount	SalesAmount	RemainingBudget
1	01	250	50	200
1	02	250	150	50
2	03	500	250	250
3	04	750	100	650
3	05	750	50	600
3	06	750	100	500

But turn it ultimately to this:

CustomerID	SalesID	StartingAmount	SalesAmount	CurrentBudget	RemainingBudget
1	01	250	50	250	200
1	02	250	150	200	50
2	03	500	250	500	250
3	04	750	100	750	650
3	05	750	50	650	600
3	06	750	100	600	500

Where CurrentBudget is always the previous amount of RemainingBudget if there are previous records, i.e. CustomerID = 1 & 3. If there are no previous records than CurrentBudget is the same amount as the StartingAmount, i.e. CustomerID = 2.

Thanks for the help so far 🙂