8 Replies Latest reply on Dec 14, 2017 12:01 PM by Joe Oppelt

    'Last 7 days' 'Last 30 days' Parameter?

    Eric Snow

      Hi guys,

       

      Can someone tell me how I would create a parameter to do this on my dashboard? This is a dashboard that will be housed on the server so new dates will come in every day. Additionally, I have a date slider currently that enables user to pick any date range. I don't want to use the relative date filter. This date range slider is also supported by a parameter that would switch the users chart axis unit0 from 'day' 'month' 'year'.

       

      Thanks

        • 1. Re: 'Last 7 days' 'Last 30 days' Parameter?
          Joe Oppelt

          Need more detail.


          And a sample workbook would really help out here.

           

          When you want to show "last 7 days", for example, do you want 7 dates on the axis?  Or do you want to grab a sum of the last 7 days on each mark -- for example on Oct 10 you would show a sum from Oct 4-10, and on Oct 11 would be a sum from Oct 5-11, etc.?

           

          The slider for a date range -- is that a filter?  If it is, you will chop off all dates before the min date, and if you want to show a sum of prior 7 days, there will be no records for them in the underlying table.

           

          A working example would help so we can play with what you have and help you create what you want.

          • 2. Re: 'Last 7 days' 'Last 30 days' Parameter?
            Eric Snow

            Last 7 days of Sales is what I want to show each day as a individual data point with respective sales.

             

            yes slider for date range is a filter. Wondering if this is possible.

            • 3. Re: 'Last 7 days' 'Last 30 days' Parameter?
              Joe Oppelt

              A sample workbook would really help.

              • 4. Re: 'Last 7 days' 'Last 30 days' Parameter?
                Hari Ankem

                Would something like this help?

                Capture.PNGCapture.PNG Capture.PNG

                 

                If yes, then the workbook is attached and you can see the solution in it.

                • 5. Re: 'Last 7 days' 'Last 30 days' Parameter?
                  Eric Snow

                  I would like a parameter on right that would be 'last 7 days', last 2 weeks, last month  etc' While keeping the date slider.

                  • 6. Re: 'Last 7 days' 'Last 30 days' Parameter?
                    Joe Oppelt

                    There is a table calc function called WINDOW_SUM() that lets you sum up a series of marks.  If you don't specify a starting offset and ending offset, it does the whole table.  But you can do this:

                     

                    WINDOW_SUM( SUM([Sales]), -6, 0 )

                     

                    And that will add up 7 days (prior 6 plus current).  You can also use variables in the offset arguments.


                    So you can set a variable based on which parameter value the user selected and use that for the first offset.

                     

                    As for the slider, if you use a table calc as your filter, you won't throw away the rows in the underlying table, which would leave them available to use in the WINDOW_SUM calc.

                     

                    These things are far easier to show in an actual example than to describe.  If my description above gets you going, then cool!  But if you need help setting them up, I'll need a sample workbook to show you in.

                     

                    If you are concerned about posting proprietary data here, then check out this video:

                     

                    Anonymize your Tableau Package Data for Sharing

                    • 7. Re: 'Last 7 days' 'Last 30 days' Parameter?
                      Eric Snow

                      Joe I attached the sample work book above. If you could create the parameter just showing last 3 months that would be great. Its pretty much exact to what's in my actual workbook.

                      • 8. Re: 'Last 7 days' 'Last 30 days' Parameter?
                        Joe Oppelt

                        (V 10.2 here)

                         

                        In the attached I created a parameter [Select a range] and put it on the sheet.  I created a calc to set the offset in the window_sum based on the value of the parameter.  [How far to look back?].  I used that offset in the Window_sum table calc.  [Rolling sales].  On this sheet both Sun(sales) and [Rolling Sales] are displayed in text format so you can see what the calc is doing.  No filter yet.  This shows all data.  You'll notice that for the first 6 days it sums up like a running sum because on day 2 there are only two days to add up.

                         

                        On Sheet 2(2) I added a filter calc that uses a table calc.  The LOOKUP function tells tableau to look at offset (whatever) to get the requested value.  When I use offset 0, it says "look right in this cell!"  When I use this calc as a filter, Tableau actually gives me full filter behavior, and I can make a slider with it.  But the cool thing about a table calc filter is that it doesn't eliminate rows from the underlying table like a quick filter does.  So if you look at 1/10/2013, for example, you see that the rolling value is 4914.  If you set the min value in the filter to 1/1/2013, the rolling value for that date is still 4914 because all the rows in the underlying table are still in there.


                        Sheet 2(3) is just a line chart of the same data.

                        1 of 1 people found this helpful