# 3+ Consecutive days

I'm creating a card where it shows the 3+ Consecutive days if the Delegate is No

I only want to display 3+ consecutive N but right now its' displaying all the dates.

(CASE WHEN

`Delegate Y/N` ='N'

AND

DATE_SUB(`Date`, Interval 3 day) THEN 1 ELSE 0 END)

• Coach

You are going to need to utilize some rank and window functions to determine if you have 3 or more consecutive dates. I would recommend using Magic ETL and the Rank & Window tile. You would use the Lag function to check for consecutive days for starters.

**Check out my Domo Tips & Tricks Videos

**Make sure to any users posts that helped you.
• Coach

You can utilize a beast mode LAG and LEAD functions to determine if it's in a 3+ day run.

LAG 1 and LEAD 1 = Day with one before and after

LAG 2 = Day with two before

LEAD2 = Day with two after

```CASE
WHEN (LAG(MAX(`Date`)) OVER (ORDER BY `Date`) = `Date` - INTERVAL '1' DAY AND
LEAD(MAX(`Date`)) OVER (ORDER BY `Date`) = `Date` + INTERVAL '1' DAY) THEN 'INCLUDE'
WHEN LAG(MAX(`Date`)) OVER (ORDER BY `Date`) = `Date` - INTERVAL '2' DAY THEN 'INCLUDE'
WHEN LEAD(MAX(`Date`)) OVER (ORDER BY `Date`) = `Date` + INTERVAL '2' DAY THEN 'INCLUDE'
END

```

Then just filter on this for any record with INCLUDE

**Did this solve your problem? Accept it as a solution!**
• the problem with window functions in analyzer in this context is that you MUST show date on the axis in order for the lag function to work.

I assume you'd only want to show rows if there were 3 consecutive days of xyz status.

if my assumption is correct, you'd want to implement your lag calc in ETL so that you have a column to work with as @MarkSnodgrass suggests.

you may want to implement lag(date, 1) , lag(date, 2), lag(date, 3) and add them as columns. From there you could use datediff functions in beast mode to decide whether to display the columns or not.

Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨‍💻

**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
• I'm so new to this and having a hard time with lag() & lead().

Yes you have assumed it right "I assume you'd only want to show rows if there were 3 consecutive days of xyz status."

This is really helpful and helps me understand what exactly needs to be done, I will play around with this.

Thank you!

• @jaeW_at_Onyx I'm not sure if I'm doing this right.

If you look at the picture on Oct 5th where it says 'Y' it shows the next 2 dates Sept 29th & Sept 28th but both of those have 'N'.