Need to Show Actual Start and End Dates, not "This Week/Last Week/Last 2 Weeks"

Options
omnicarson
omnicarson Member
in Dashboards

G'day everyone 👋 I'm trying to use Smart Text (dynamic text) to show the dates being filtered, however the text comes through just as the filter looks (eg. This Week, Last Week, Last 2 Weeks, etc). Ideally, we'd like to show the actual dates being reported (eg. 08/12/2024 - 08/18/2024). I am a bit stuck trying to make this happen - any ideas/help would be greatly appreciated!

The images below show how it currently looks/behaves. What we'd like is for that text in the orange box to be the actual dates of the last week*, so the text there would read 08/12/2024 - 08/18/2024.

*We have a fiscal calendar where the week is Monday-Sunday, and this is in App Studio.

Truly appreciate any help or guidance. Thank you!

Screenshot 2024-08-19 at 12.34.18 PM.png

Screenshot 2024-08-19 at 12.34.23 PM.png
Tagged:

Best Answers

  • ArborRose
    ArborRose Coach
    Answer ✓

    I don't do much with Smart Text but I think you'd need to create calculated fields to dynamically calculate the start and end dates with a label for the period you need.

    Start Date of Last Week:

    DATE_SUB(CURRENT_DATE(), INTERVAL (WEEKDAY(CURRENT_DATE()) + 6) DAY)

    This calculates the Monday of the previous week.


    End Date of Last Week:

    DATE_SUB(CURRENT_DATE(), INTERVAL WEEKDAY(CURRENT_DATE()) DAY)

    This calculates the Sunday of the previous week.

    Make sure the calculated fields are include on your dataset. Then use the calculated fields in the smart text something like

    Last Week: {Start_Date_Field} - {End_Date_Field}

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

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    you could also a summary number in your smart text to select the min and max date values

    Alternatively you can do this in a beast mode and add the beast mode as smart text

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

Answers

  • ArborRose
    ArborRose Coach
    Answer ✓

    I don't do much with Smart Text but I think you'd need to create calculated fields to dynamically calculate the start and end dates with a label for the period you need.

    Start Date of Last Week:

    DATE_SUB(CURRENT_DATE(), INTERVAL (WEEKDAY(CURRENT_DATE()) + 6) DAY)

    This calculates the Monday of the previous week.


    End Date of Last Week:

    DATE_SUB(CURRENT_DATE(), INTERVAL WEEKDAY(CURRENT_DATE()) DAY)

    This calculates the Sunday of the previous week.

    Make sure the calculated fields are include on your dataset. Then use the calculated fields in the smart text something like

    Last Week: {Start_Date_Field} - {End_Date_Field}

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

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    you could also a summary number in your smart text to select the min and max date values

    Alternatively you can do this in a beast mode and add the beast mode as smart text

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

    Thank you @ArborRose and @GrantSmith. Truly appreciate the input. Going to test those solutions, fingers crossed! Thanks again!

Categories