Dynamic Dates

nj-John-mirc
nj-John-mirc Member
in Charting

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

Answers

  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **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.
  • nj-John-mirc
    nj-John-mirc Member

    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
    MarkSnodgrass Coach

    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.

    **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.

Categories