Dynamic Dates

nj-John-mirc

I hope I can explain this clearly - I need to create a subset of the date field which is fluid and changes as the dataset updates. For example: Last-16 days thru last-8 days(so if last-16 =9/16 and Last-8= 9/8, then the next update it will be 9/17 - 9/9)

It's similar to BETWEEN RANGE in Analyzer, however the Between Range is constant on the dates selected.

Thanks

MarkSnodgrass

If I understand you correctly, you want to always show the data where the date is between 8 and 16 days ago and not have to update any logic as your dataset gets new data. You can do this by creating a beast mode and use the datediff function to determine if the data is within your range. It would look like this:

CASE WHEN DATEDIFF(CURRENT_DATE(),`TransDate`) >= 8 AND DATEDIFF(CURRENT_DATE(),`TransDate`) <= 16 THEN 'Include' 
ELSE 'Exclude' 
END

You would then drag this field into your filters and filter to include.

I have a video that may help you as well.

https://youtu.be/qxhV3gAZnk4

Hope this helps.

nj-John-mirc

Hey Mark,

Yes, this will work! However, I realized the dataset is a weekly update, meaning the currdate function will not work. Is a function available that grabs the last/most current date in the dataset?

John

MarkSnodgrass

If you have the batch_last_run date as a column in your dataset that stamps when Domo last ran it, you could use that field.

You could also use MAX(`datefield`) OVER() , but this only works if you have the "window functions in beast modes" feature turned on. Your CSM can turn it on for you, if it isn't.

You also might still be able to use the current date, but do some additional logic to account for what day of the week it is to get the date range to move along with it.