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)

 


Comments

  • 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 <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • Hi @Hiraayub

    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

    **Was this post helpful? Click Agree or Like below**
    **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'.