Working with overlapping dates

[Sabari Vishnu Jayanthan Jaikrishnan]

Hi Team, I just started to work on spotfire yesterday. I am stuck in this particular stage and I don't know how to proceed.

Consider, a [case]is "confirmed" on 8th feb. Then the [status] for that particular [bus_id] on that particular [qr_creation_date] should be "red". If no cases means green(I am able to achieve this).

 

The tricky part is I would like to change the [status] to "yellow" 14 days after the recent confirmed case. for example - 14 days after 8th should be yellow. But on 10th there is another confirmed case, so it should be 14 days after 10th should be in "yellow". The days before 8th should be green.

I tried many expressions but I would like to get help from the experts. any help would be really appretiated here.