Set Analysis - Get Max Value From Previous Date

[kenn]

Hello,

I need help with my pivot control.

How do I pull the max value of the previous date's "Remaining" field. 

I want to add the previous date's max value to the "Delivered" of the existing date.

The data looks like this:

Store	Worker	Date	Delivered	Remaining
Store 1	Worker 1	6/9/2020	20	5
Store 1	Worker 2	6/9/2020	20	6
Store 1	Worker 1	6/4/2020	30	10
Store 1	Worker 2	6/4/2020	30	8
Store 1	Worker 1	6/1/2020	50	2
Store 1	Worker 2	5/28/2020	60	20
Store 2	Worker 3	6/10/2020	20	5
Store 2	Worker 4	6/10/2020	20	6
Store 2	Worker 3	6/3/2020	30	15
Store 2	Worker 4	6/3/2020	30	8
Store 2	Worker 3	5/31/2020	50	2
Store 2	Worker 4	5/28/2020	60	20

Essentially, I want the pivot to look like this:

Store

6/10/2020

6/9/2020

6/4/2020

6/3/2020

Delivered

Remaining

Actual work to do

Delivered

Remaining

Actual work to do

Delivered

Remaining

Actual work to do

Delivered

Remaining

Actual work to do

Store 1

20

6

30 (the formula is 20+10)

30

10

32 30+2

Store 2

20

6

35 (the formula is 20+15)

30

15

32 30+2

Thank you so much.

[Saravanan_Desingh]

One solution is .

tab1:
LOAD RowNo() As RowID, * INLINE [
    Store, Worker, Date, Delivered, Remaining
    Store 1, Worker 1, 6/9/2020, 20, 5
    Store 1, Worker 2, 6/9/2020, 20, 6
    Store 1, Worker 1, 6/4/2020, 30, 10
    Store 1, Worker 2, 6/4/2020, 30, 8
    Store 1, Worker 1, 6/1/2020, 50, 2
    Store 1, Worker 2, 5/28/2020, 60, 20
    Store 2, Worker 3, 6/10/2020, 20, 5
    Store 2, Worker 4, 6/10/2020, 20, 6
    Store 2, Worker 3, 6/3/2020, 30, 15
    Store 2, Worker 4, 6/3/2020, 30, 8
    Store 2, Worker 3, 5/31/2020, 50, 2
    Store 2, Worker 4, 5/28/2020, 60, 20
];

tab2:
LOAD DISTINCT Store, Date, Max(Delivered) As MaxDeli, Max(Remaining) As Max_Rem
Resident tab1
Group By Store, Date;

tab3:
NoConcatenate
LOAD Store, Date, MaxDeli As Delivered, Max_Rem , Peek('Max_Rem') As Max_Rem2, If(Store=Peek(Store),MaxDeli+Peek('Max_Rem')) As [Actual work to do]
Resident tab2
Order By Store, Date Asc;

Drop Table tab1, tab2;

[Saravanan_Desingh]

[kenn]

Ok, thank you for your response.