3+ Consecutive days

Hiraayub · 2021-10-26T16:23:49+00:00

There was an error rendering this rich post.

Hiraayub

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)

Find more posts tagged with

Comments

MarkSnodgrass

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

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

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

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

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