Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Customers, Partners & Luminaries only: You're invited to a Data Analytics Roadmap session. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
Contributor

Set Analysis - Get Max Value From Previous Date

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:

StoreWorkerDateDeliveredRemaining
Store 1Worker 16/9/2020205
Store 1Worker 26/9/2020206
Store 1Worker 16/4/20203010
Store 1Worker 26/4/2020308
Store 1Worker 16/1/2020502
Store 1Worker 25/28/20206020
Store 2Worker 36/10/2020205
Store 2Worker 46/10/2020206
Store 2Worker 36/3/20203015
Store 2Worker 46/3/2020308
Store 2Worker 35/31/2020502
Store 2Worker 45/28/20206020

 

Essentially, I want the pivot to look like this:

Store6/10/20206/9/20206/4/20206/3/2020
 DeliveredRemainingActual work to doDeliveredRemainingActual work to doDeliveredRemainingActual work to doDeliveredRemainingActual work to do
Store 1   20630
(the formula is 20+10)
301032
30+2
   
Store 220635
(the formula is 20+15)
      301532
30+2

 

Thank you so much.

Labels (3)
3 Replies
Highlighted
Specialist III
Specialist III

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;
Highlighted
Specialist III
Specialist III

commQV26.PNG

Highlighted
Contributor
Contributor

Ok, thank you for your response.