topic Need to compare previous records and identify the date difference in App Development

Need to compare previous records and identify the date difference

Siraj — Tue, 20 Jun 2023 16:11:48 GMT

Hi,

Please refer the below table, we have Item ID , color status and Date (weekly snapshot Date) my scenario is latest color status check previously changed color status and there is change means need to compare two dates and provide number of days difference

Latest Color = Yellow 5/8/2023

Previous Color = Red 4/21/2023

EX: Max(5/8/2023) - Min(Red4/21/2023 ) = 17 Days difference

Kindly help to achieve this in Qliksense script

Thanks in Advance

Item ID	Color Status	Date
10056328	Unassigned	3/10/2023
10056328	Unassigned	3/17/2023
10056328	Yellow	3/24/2023
10056328	Yellow	3/31/2023
10056328	Red	4/7/2023
10056328	Red	4/14/2023
10056328	Red	4/21/2023
10056328	Yellow	4/28/2023
10056328	Yellow	5/5/2023
10056328	Yellow	5/8/2023

Re: Need to compare previous records and identify the date difference

Benoit_C — Wed, 21 Jun 2023 08:53:09 GMT

Hi @sirine93,

Can you please check if below similar conversation is helpful ?

https://community.qlik.com/t5/New-to-Qlik-Sense/Difference-between-date/td-p/1788764

Regards,
Benoit

Re: Need to compare previous records and identify the date difference

Siraj — Thu, 22 Jun 2023 17:41:57 GMT

Hi Benoit,

Thanks for your reply,

but this is not solution for my Scenario. I need to compare two colors (Previous value) in same column based on changes in color I need to find Date difference.

Re: Need to compare previous records and identify the date difference

Benoit_C — Mon, 26 Jun 2023 10:04:37 GMT

Hi @Siraj,

I've just tried to load same table and rows than you and I managed to get the number of days when color is changed with Peek function (https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/Scripting/InterRecordFunctions/Peek.htm)

My script below:

RawData:
LOAD * INLINE [
Item ID, Color Status, Date
10056328, Unassigned, 3/10/2023
10056328, Unassigned, 3/17/2023
10056328, Yellow, 3/24/2023
10056328, Yellow, 3/31/2023
10056328, Red, 4/7/2023
10056328, Red, 4/14/2023
10056328, Red, 4/21/2023
10056328, Yellow, 4/28/2023
10056328, Yellow, 5/5/2023
10056328, Yellow, 5/8/2023
];

TempTable:
LOAD
[Item ID],
[Color Status],
[Date],
If([Color Status] <> Peek([Color Status]) and [Item ID] = Peek([Item ID]), [Date]) as ColorChangeDate
Resident RawData
Order By [Item ID], [Date];

FinalTable:
LOAD
[Item ID],
[Color Status],
[Date],
[ColorChangeDate],
If([Item ID] = Peek([Item ID]) and [Color Status] = Peek([Color Status]), [Date] - Peek([ColorChangeDate])) as DateDifference
Resident TempTable
Order By [Item ID], [Date];

DROP Table TempTable;
DROP Table RawData;

Regards,
Benoit