1 2 Previous Next 16 Replies Latest reply on Mar 27, 2012 11:18 AM by Joe Crum

    Filtering dates

      We use Tableau to show what different advertisers are spending on television over specific flight dates. So, for example, AT&T could have spent $1 million from September 25th through October 1st. The data is entered into our database as such. But what if I wanted to show the spending from September 27th through September 30th. How would I filter these dates in Tableau? Thanks!

        • 1. Re: Filtering dates
          Alex Kerin

          Does the data exist, or are you looking to interpolate between the dates?

          1 of 1 people found this helpful
          • 2. Re: Filtering dates

            The data would have been entered as start date 9/25, end date 10/1, so we would have to interpolate between the dates or divide up the spending into days. Let me know how I could handle. Thank you!

            • 3. Re: Filtering dates
              Alex Kerin

              Without knowing how your data or chart is setup, the calculation may look like this:

               

              Daily Spend

              sum([spend])/datediff('day',min([date]),max([date])

               

              There's a good chance that it may need to be table calculations.

              • 4. Re: Filtering dates

                Thanks. Would this calculation be made under the dollars or the year?

                • 5. Re: Filtering dates

                  Attached is a packaged workbook to show you the view. Rather than showing the exact dates of the spending - I want to be able to show a real week - so 1/19-1/25, 1/26-2/1 for example.Let me know if you can help. Thanks!

                  • 6. Re: Filtering dates
                    Alex Kerin

                    Year to date spend isn't really year to date is it - more the cost for that segment?

                     

                    EDIT: I think this may be a queue problem (3rd I've encountered recently) as we need to pad the days in between start and stop with the amount per day spent

                    • 7. Re: Filtering dates

                      YTD$ is the cost of that segment, correct.

                      • 8. Re: Filtering dates
                        Joe Crum

                        Maybe I don't take the optimal approach but how I handle this is by creating two parameters and a calculated field.  I create a StartDate & EndDate parameter and a DateKeeper calculated field with the below formula.  I then drop the DateKeeper field in the filters and select to filter out the records labeled "Remove".

                         

                        IF [scanDate] >=[StartDate] AND [scanDate] <= [EndDate] THEN "Keep"

                        ELSE "Remove"

                        END

                        • 9. Re: Filtering dates

                          Thanks. Where do I set this up?

                          • 10. Re: Filtering dates
                            Joe Crum

                            Both the Parameters and the Calculated Fields can be created by right clicking in the white space in Dimensions or Measures on the right side of the Workbook.  The formula from my original post would go in the Calculated Field.  After you add a Parameter they will show up in a section below the measures.  (See picture below)

                             

                            Creating Parameter.png

                            • 11. Re: Filtering dates

                              Hi Joe -

                               

                              I am completely confused by this. Would you mind writing out a step by step for me? I am a newbie to Tableau. Thanks!

                              • 12. Re: Filtering dates
                                Joe Crum
                                1. Add a StartDate Paramter – Right click in the white space below your Measures and select “Create Parameter…”
                                  1. Set Name to StartDate
                                  2. Change Data Type to Date
                                  3. Set you Current Value to you first date you want to include
                                  4. Click OK
                                2. Add a EndDate Paramter – Right click in the white space below your Measures and select “Create Parameter…”
                                  1. Set Name to EndDate
                                  2. Change Data Type to Date
                                  3. Set you Current Value to you last date you want to include
                                  4. Click OK
                                3. Add a DateKeeper Calculated Field – Right click in the white space below your Measures and select “Create Calculated Field…”
                                  1. Set Name to DateKeeper
                                  2. Paste the following formula into the Formula section.

                                IF [scanDate] >=[StartDate] AND [scanDate] <= [EndDate] THEN "Keep"
                                ELSE "Remove"
                                END

                                1. Replace scanDate with the name of your date field
                                2. Click OK
                                3. Show Parameter Controls (These will be used to allow what is shown on the screen to be dynamic)
                                  1. Right click on the StartDate Parameter and select “Show Parameter Control”
                                  2. Right click on the EndDate Parameter and select “Show Parameter Control”
                                4. Add DateKeeper to Filter Shelf
                                  1. Drag the new calculated field (DateKeeper) from the dimensions to the filter shelf
                                  2. Check the box next value “Keep”
                                  3. Click OK
                                5. Adjust Parameters & Enjoy
                                • 13. Re: Filtering dates

                                  Thanks Joe - this is perfect. Makes total sense. If I wanted to be able to view the newly created Parameters as Columns (to show their values above the measures) would I be able to do that? Would I also be able to do multiple parameters - to show the previous 3 weeks for example?

                                   

                                  Also - is there a way to show daily spending? So for example, data with a flight of 1/3-1/9 would be divided by 7, then each date would be assigned the appropriate data. Does that make sense?

                                   

                                  Thanks again!

                                  • 14. Re: Filtering dates
                                    Joe Crum

                                    The parameters are just single data values that are being used in the calculated field.  You can display there value as a column but there is just one value per parameter.  You could use the values of the parameters in a title to describe the data in the view. (see attached workbook)

                                     

                                    You could create additional parameters to add additional conditions.  Like looking at your workbook you attached you could create parameters and calculated fields for both the FlightStart and the FlightEnd.  You could then filter on both to narrow the data you are working with.  (see attached workbook)

                                     

                                    I have updated your workbook and I am attaching in to show a Flight Duration Calculation and a Points Per Day Calculation.  I am not sure I used the field you wanted to use but it should show you how you could make such a calculations.  I think this should answer you question.

                                    1 2 Previous Next