Need to compare previous records and identify the date difference

[Siraj]

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

[Benoit_C]

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

[Siraj]

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.

[Benoit_C · Accepted solution]

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/InterRecordFunc...)

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