Dynamic aggregation based on user selected date grain

david_cunningham
david_cunningham Coach
in Beast Mode

I have a dataset that appends daily to build out a historical view. I want to be able to always show the latest record per date grain based upon the user selected date range and grain.

For example, if a user selected YTD by month, then I would want to show the following values from the last day of each month

SUM(values) from Jan 31st snapshot (ignore all other snapshots in Jan).

SUM(values) from Feb 28th snapshot (ignore all other snapshots in Feb).

SUM(values) from March 31st snapshot (ignore all other snapshots - in Mar), etc.

If a user selected current YTD by Week, I would want to show the following values from the last available snapshot of each week.

SUM(values) from the last day of week 1 (ignore all other snapshots in week1)

SUM(values) from the last day of week 2 (ignore all other snapshots in week2, etc.

I want to avoid doing this in ETL if possible, because I want it to be as responsive as possible and work across a variety of user selected date ranges/grains.

I've been playing around with window/fixed functions, but can't seem to get them to work. Details below

image.png
image.png

David Cunningham

** Was this post helpful? Click Agree 😀, Like 👍️, or Awesome ❤️ below **
** Did this solve your problem? Accept it as a solution! ✔️**

Tagged:

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Beastmodes don't have access to the selected date grain on the date selector so you won't be able to do it that way. You could utilize a variable to select the date grain which you would have access to.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    Beastmodes don't have access to the selected date grain on the date selector so you won't be able to do it that way. You could utilize a variable to select the date grain which you would have access to.

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • ggenovese
    ggenovese Contributor

    Having the date selector values available as system variables is exactly the kind of thing I was hoping for when I made this enhancement request, please vote for it if you're interested in seeing this functionality available.

    https://community-forums.domo.com/main/discussion/68333/smart-text-as-a-variable

Categories