2 Replies Latest reply on May 9, 2017 11:42 AM by David Uslan

    Weekly Data Always Ending Yesterday (Dynamic Datetrunc)

    David Uslan

      I'm creating a dashboard that has a report showing weekly stats. I need this report to update daily, but show the most recent seven days instead of week to date. I know this is possible with DATETRUNC and setting a static weekday as the optional 3rd argument, but I haven't found a way (through several attempts and much forum browsing) to make that weekday dynamic.


      Explaining further if necessary:

      Today is Monday May 8th. The WEEK([Created Date]) pill is showing the last data point to be May 7 (Sunday, start of week).


      I can manually get what I want displayed with this calc, which starts the day on the previous Monday and ends yesterday:

      Datetrunc('week', [Created Date], 'monday')


      All attempts to pass in a "non literal" string that tries to resolve to 'monday' results in the following error: "Error: Argument 3 to datetrunc must be a(n) string literal"


      TLDR: I want to be able to always have the dayofweek be today's day of week so each week has a full week of data. How can I do this?

      Thanks for your help.


      (Similar question to issues trying to make the weekday part in datetrunc function dynamic )

        • 1. Re: Weekly Data Always Ending Yesterday (Dynamic Datetrunc)
          Chris Hopkinson

          Hi David,


          I think I'm off the mark here ... but can you not use

          [Date]>=today()-7 and [Date]<=today()-1 within the calculated field?

          This will keep it rolling.


          We've also used a "Show/Hide" in the past for some sheets that works well.


          Sample book attached, but if you're able to attach a sample similar to what you are looking for that would be good too.





          • 2. Re: Weekly Data Always Ending Yesterday (Dynamic Datetrunc)
            David Uslan

            Hey Chris,


            Thanks for the response. I'm not sure your solution will work for my case. I don't think I was clear about the fact that I'm trying to make this work specifically in a line chart showing weekly values for a calculated ratio. I can't really share the workbook I'm working on due to sensitive business data but here's a screenshot.




            The left viz is just the WEEK(Created Date). The right viz is Day(Created Calc) where the calc is: Datetrunc('week', [Created Date], 'tuesday')


            Since today is Tuesday and I want the last datapoint to always be a full week ending yesterday, anyway to get 'tuesday' to be dynamically set to today's day of week is what I'm looking for.


            Hopefully that clarification makes sense, but let me know if not.