Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
thanks for your answer, but is there any way to do this via chart function and avoid script ?
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
EDIT : solved a last issue, thanks a lot it works !