Best Of

Hi @user041053

You'll need to utilize a window function and a date diff function to calculate the difference between the two dates.

DATE_DIFF(`Date`, COALESCE(LAG(`Date`) OVER (ORDER BY `Date`)),`Date`))

DATE_DIFF is calculating the number of days between the two dates

LAG(`Date`) OVER (ORDER BY `Date`) gets the Date value from the prior row, ordered in ascending based on the Date value

COALESCE is defaulting the date to be either the prior date, or if it's null / the first record it will return the same date to get you a 0 difference.

@user04105

I'm working with too many different SQL version, so sorry! It should be DATEDIFF not DATE_DIFF.

Domo has some issues processing with the coalesce inside and not populating the date correctly. This modified version does the same and sets the difference to 0 if it's the first date in the partition.

COALESCE(DATEDIFF(`dt`, LAG(`dt`) OVER (ORDER BY `dt`)), 0)

Hi @LoveBindal

Have you looked into Window functions? You can do it in either an ETL using the Rank and Window tile (https://domohelp.domo.com/hc/en-us/articles/360042922814-Magic-ETL-Tiles-Rank-and-Window) or you can use window functions within beast modes. Specifically utilizing LAG and LEAD will allow you to get the N prior row or the X next row. I've done a writeup of how to use LAG and/or LEAD for a rolling 3 day average here: https://dojo.domo.com/discussion/52679/domo-ideas-conference-beast-modes-rolling-averages#latest

So this isn't easily possible however you could implement a mysql data flow using some creative hackery using variables. I was able to write up an example using your sample data you had provided to get the results you expected.

SELECT `Dept`,`Value`, COALESCE(`Value`, IF(`tbl`.`Dept`=@pred, @prev+@offset, @offset+@prev-1.1)) as new_value, @offset, CASE WHEN `Value` IS NOT NULL THEN @prev:=`Value` END prev, @pred:=`Dept` pred, @offset:=1+case when `Value` IS NULL THEN @offset ELSE 0 END new_offset
FROM `tbl`, (SELECT @pred := null, @prev := null, @offset:=0) var_init

Essentially it's keeping track of how much offset to add if the current value is null. It's not a perfect working version but gives you an idea. If a new department starts with nulls it won't handle that scenario properly for the 2nd null row in a department.