4 Replies Latest reply on Oct 14, 2013 8:48 AM by Amy Song

    Display last 10 weeks from week selected

    Amy Song

      Hi, I've been wondering how to do this for a while (applies to more than just this specific example)...


      I have a dashboard with a filter on week number, and some components will show activity for that week, but I also want to include components that show trends over a longer period of time.


      The relative date filter can be used to display "last 10 weeks" from "today" or it can be anchored to a static date.  But can it be anchored dynamically, to the max date available instead?  So if the week number filter is changed, the "last 10 weeks" trend also changes?



        • 1. Re: Display last 10 weeks from week selected
          Joshua Milligan



          I've attached a workbook to demonstrate a possible approach.  You may wish to modify it slightly for your specific case.  The approach is to use the Last() function to determine the last 10 weeks from the maximum date in the data returned (so the filter will change the max value).  In this view, Last() is calculated table across and so the assumption is that each mark across the table is one week -- if your view is different it may require some adjustment.  I'd be happy to help or answer additional questions if needed!





          Last 10 Weeks.twbx.png

          1 of 1 people found this helpful
          • 2. Re: Re: Display last 10 weeks from week selected
            Amy Song

            Hi Josh, thanks for the response!


            I'm actually looking for something slightly different... see attached modified workbook.


            The selection in my existing workbook is week number, and I have many components showing data for the week selected (in the attached example, there is the one "daily" component shown).  So I already have that filter estabilshed, which is a bit different from the filter you have.  Is it possible to show the last 10 weeks trend in that set up?



            • 3. Re: Re: Re: Display last 10 weeks from week selected
              Matt Lutton

              If you replace your filter with his "Week of Order Date" (maximum date) filter, and place the parameter control on the dashboard, it will affect the bottom chart only as it sits.  Since your Week #s represent actual weeks in the data, this works just fine.


              See attached; does this help at all or is there a problem with it?  You could use a different type of date filter, and the parameter will still work.

              1 of 1 people found this helpful
              • 4. Re: Display last 10 weeks from week selected
                Amy Song

                Not quite... I am interested in seeing weeks by week number, so the max date filter didn't quite get me there, since it showed a rolling 7 days instead of calendar weeks.


                I was able to do it though, with a pararameter for integer where you type in week number, and using the calculated fields:


                Show one week: datepart('week',[Date])=[Week number parameter]

                Show last 10 weeks: datepart('week',[Date])<=[Week number parameter] and datepart('week',[Date])>=[Week number parameter]-9


                Thanks for your responses though, they helped me think through the task!