3+ Consecutive days

Hiraayub
Hiraayub Fort Myers, FL ⚪️

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

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    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.

  • GrantSmith
    GrantSmith Indiana 🔴

    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

  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🟤

    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.

  • Hiraayub
    Hiraayub Fort Myers, FL ⚪️

    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!

  • Hiraayub
    Hiraayub Fort Myers, FL ⚪️

    @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'.