How to find the first value before a date offset?

[peterstalberg]

Hi Folks,

I have a list of products changing price over time, se example below. Now I want to list/analyse the current price 180 days after the initial listing only.

An elegant(?) solution for this would be highly appreciated. Thanks//Peter

INPUT TABLE

PRODUCT	PRICE	DATE
PRODUCT A	15 000	2011-09-07
PRODUCT A	25 000	2011-11-30
PRODUCT A	75 000	2012-06-28
PRODUCT A	165 000	2014-10-14
PRODUCT A	200 000	2014-11-10
PRODUCT A	130 000	2015-06-23
PRODUCT B	5 000	2018-01-19
PRODUCT B	15 000	2018-05-19
PRODUCT B	22 000	2019-01-07

OUTPUT TABLE (<=180 days from initial listing)

PRODUCT	PRICE	DATE
PRODUCT A	25 000	2011-11-30
PRODUCT B	15 000	2018-05-19

[NitinK7]

Hi peter 

PRODUCT	PRICE	DATE
PRODUCT A	15 000	2011-09-07

your  product A initial date is 2011-09-07 then you want after 180 days of initial date

so date should be 2012-05-12 but your output table is 2012-06-28 for product A and same as Product B

please confirm

[peterstalberg]

oops! My mistake fiddling with the dates. I have updated the example accordingly. Yes, it is the current price at 180 days after the initial listing . Hope it is clearer now. Thanks//Peter

[NitinK7]

Hi 

try this

ABC:
LOAD PRODUCT,
PRICE,
DATE
FROM
[Try and Error\first value after 180.xlsx]
(ooxml, embedded labels, table is Sheet1);

Inner Join
PQR:
LOAD PRODUCT,

Date( FirstSortedValue(DATE,DATE)+180) as DATE
Resident ABC
Group by PRODUCT;

[peterstalberg]

Hi NitinK7,

Thank you very much for your proposed solution. This seems to work within the script but, if possible,  I was rather looking for a solution as a Chart Function within the app. Do you think there is one? Rgds//Peter