Replace an value between 2 dates (Conditions)

[Calogero_Alfeo]

Hi dear QlikSense teammates,

Hope you are doing well.

I would like to create a condition to replace a value by another one between two dates. 

I have a specific example heureunder:

I would like to replace the value 'Jour férié' in the third lines by 'Maladie'.

2 Conditions :

1) 'Jour férié' is surrounded by the value 'Maladie'.

2) The dates must follow each other (31/12/2021 -> 01/01/2022 -> 02/01/2022)

Thank you,

Regards.

Calo

[Mario_De_Felipe]

Assuming your table is order by date ascending (you can force it using Table order properties), I think what you want is something like this:

if(above([Field])='Maladie' and [Field]='Jour férié' and before([Field])='Maladie' and above([DateField]=Date([DateField]-1) and before([DateField])=Date([DateField]+1),'Maladie',[Field])

Hope it helps