SOLVED

show current months purchases but include previous price paid

Copper Contributor

Hi All

I need to create a spreadsheet listing all stock codes purchased from a selected time frame (usually current month). Additionally I need to show the previous price paid against each one. I have tons of data, but for testing purposes I've simplified it down to this. I've manually put this together, but screenshots and tables posted below.

 

I thought perhaps some sort of index lookup? or use of FILTER? But I'm not sure how to achieve this. O

 

pmsilv_1-1693492572164.png

 

pmsilv_2-1693492592898.png

 

 

Stock codePurchase DateQtyUnit Price
ABC12301/01/2000110
DAB31201/01/2000122
PJY55501/01/20001150
ABC12302/02/20051520
DAB31202/02/20051533
PJY55502/02/200515180
ABC12303/03/2010330
DAB31203/03/2010344
PJY55503/03/20103202
ABC12304/04/20112040
DAB31204/04/20112055
PJY55504/04/201120531
ABC12305/05/20155550
DAB31205/05/20155566
PJY55505/05/201555667
ABC12306/06/2022260
DAB31207/06/2022577
PJY55503/07/20224808
ABC12301/08/2023970
DAB31201/08/2023988
PJY55501/08/20239734

 

Desired OutputItems purchased this month      
Stock codePurchase DateQtyUnit PriceLast PurchasedLast QtyLast Unit PriceDiff
ABC12301/08/202397006/06/202226017%
DAB31201/08/202398807/06/202257714%
PJY55501/08/2023973403/07/20224808-9%

 

 

4 Replies
best response confirmed by pmsilv (Copper Contributor)
Solution

@pmsilv  it ain't pretty but here is something that seems to work:

=LET(filt_data,FILTER(Data,(Data[Purchase Date]>=st_date)*(Data[Purchase Date]<=end_date),""),
           DROP(REDUCE("",SEQUENCE(ROWS(filt_data)),LAMBDA(p,q,
                    LET(datarow,INDEX(filt_data,q,),
                            lastdata, TAKE(SORT(FILTER(CHOOSECOLS(Data,2,4),(INDEX(filt_data,q,1)=Data[Stock code])*(Data[Purchase Date]<st_date),{0,0})),-1),
                             VSTACK(p,HSTACK(datarow,lastdata,(INDEX(datarow,4)-INDEX(lastdata,2))/INDEX(lastdata,2)))))),1))

@mtarler 

 

it ain't pretty but here is something that seems to work:

 

One might even say that it's downright ugly. :ogre:

 

But I've saved that file and hope someday to be able to understand and explain what you've done here.

John, thx for the compliment (I think).
Either way for your benefit and @pmsilv here is a quick summary of the function:
line 1 - filt_data is the data filtered for the time range requested
line 2 - set up the DROP(REDUCE(LAMBDA(... to circumvent the array of arrays
line 3 - datarow is inside the LAMBDA and indexes the filtered data to the corresponding row
line 4 - lastdata is finding the last time that item was purchased. this is the 'fun' part. there is a filter on columns 2 & 4 because we only need the date and price and filtered by corresponding part number and dates < start range. Then sort the results and TAKE the greatest date.
line 5 - HSTACK the filtered date + lastdata + calculate the % diff in pricing
and it just occurs to me that if no prior date is found then 0,0 are returned which means the % diff will be DIV BY 0 error ... but I suppose that is OK (maybe).
WOW!! @mtarler Thanks so much for the solution but also for the through explanation and example file. I'll work through this and apply it to my full data file. Kind regards Paul
1 best response

Accepted Solutions
best response confirmed by pmsilv (Copper Contributor)
Solution

@pmsilv  it ain't pretty but here is something that seems to work:

=LET(filt_data,FILTER(Data,(Data[Purchase Date]>=st_date)*(Data[Purchase Date]<=end_date),""),
           DROP(REDUCE("",SEQUENCE(ROWS(filt_data)),LAMBDA(p,q,
                    LET(datarow,INDEX(filt_data,q,),
                            lastdata, TAKE(SORT(FILTER(CHOOSECOLS(Data,2,4),(INDEX(filt_data,q,1)=Data[Stock code])*(Data[Purchase Date]<st_date),{0,0})),-1),
                             VSTACK(p,HSTACK(datarow,lastdata,(INDEX(datarow,4)-INDEX(lastdata,2))/INDEX(lastdata,2)))))),1))

View solution in original post