Calculate a period based on date (looking for value in another row)

[ThibKm]

Hello Everyone,

i'm looking for a chart function that would solve my problem

I got a lot of rows with "prices" and "dates".

For a specific date, i get a price change, and i need to calculate Total based on period (final day - last day when price was changed * Price).

unfortunately, i cannot change source of data.

Day of the month is obtained by extracting day number of date price changing

Please see below

I tried many many things without success,

Thanks a lot for your help in advance.

[MarcoWedel]

one possible solution:

table1:
LOAD *,
     If(Fruit=Previous(Fruit),Date-Previous(Date),DayOfMonth)*Price as Total;
LOAD *,
     Date(Date#(DayOfMonth&Month&Year,'DDMMMYYYY'),'MM/DD/YYYY') as Date
Inline [
Fruit, Price, Year, Month, DayOfMonth
Apple, 100, 2021, Jan, 31
Apple, 150, 2021, Feb, 10
Apple, 200, 2021, Feb, 28
Apple, 180, 2021, Mar, 20
Apple, 150, 2021, Apr, 8
Apple, 250, 2021, Apr, 30
Banana, 200, 2021, Feb, 15
Banana, 300, 2021, Feb, 28
Banana, 400, 2021, Mar, 5
Banana, 500, 2021, Mar, 25
Banana, 600, 2021, Apr, 10
];

hope this helps

Marco

[ThibKm]

thanks for your answer, but is there any way to do this via chart function and avoid script ?

[MarcoWedel · Accepted solution]

sure, one translation into a chart expression could be:

If(Fruit=Above(TOTAL Fruit),Date#(DayOfMonth&Month&Year,'DDMMMYYYY')-Above(TOTAL Date#(DayOfMonth&Month&Year,'DDMMMYYYY')),DayOfMonth)*Price

[ThibKm]

EDIT : solved a last issue, thanks a lot it works !