topic Re: Calculate a period based on date (looking for value in another row) in QlikView

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

ThibKm — Wed, 04 Jan 2023 17:17:07 GMT

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.

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

MarcoWedel — Wed, 04 Jan 2023 18:10:10 GMT

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

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

ThibKm — Thu, 05 Jan 2023 08:02:44 GMT

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

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

MarcoWedel — Thu, 05 Jan 2023 21:16:53 GMT

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

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

ThibKm — Fri, 06 Jan 2023 13:47:32 GMT

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