5 Replies Latest reply on Sep 27, 2018 1:49 PM by Joe Oppelt

    MTD Filter based on date from a Dashboard Filter?

    Chad Clauson

      Hi, I am creating a dashboard where we have two numerical rows - the first row is the daily summary which is driven by the single-date dashboard filter. Users can go back 7 individual days on the dashboard to show data only from that specific day. In the early month, some dates will go back to the previous month (ie slider could be 8/29, 8/30, 8/31, 9/1, 9/2, 9/3, 9/4) My second row is the MTD summary. This will show ALL data from the MONTH of the dashboard filter to today, not the date of the dashboard filter. This is a pretty easy formula -  Sum(if Month([Date] = Month(Today()) then [Sales] end) for this current month. However, as mentioned above, the data could be from the previous month (8/29, 8/30, or 8/31). In that case, the MTD view will show all data from August. How do I accomplish this?

        • 1. Re: MTD Filter based on date from a Dashboard Filter?
          Joe Oppelt

          I don't understand the question.

           

          Can you upload a sample workbook so I can see it firsthand?

          • 2. Re: MTD Filter based on date from a Dashboard Filter?
            Chad Clauson

            Not a tableau workbook but here's a spreadsheet showing the same thing

            • 3. Re: MTD Filter based on date from a Dashboard Filter?
              Joe Oppelt

              That kind of helps.  But I still don't understand what you're trying to do.


              Your example seems to have set the "filter" to TODAY().  That would be September.  How do you know when to dig into August?

               

              If you really meant that the user entered an August date, How do you know to dig into September?

               

              Also, I asked for a workbook because I need to see how you are applying the filter to your sheet.  Is it realty a filter?  Or is it a parameter.  (And please, I'm not asking you just to answer that question.  I really need a workbook here.)

              • 4. Re: MTD Filter based on date from a Dashboard Filter?
                Chad Clauson

                I can't provide a workbook due to confidentiality reasons but the filter is set up by applying a relative date filter last 7 days based on [Date]. Then I applied another [Date] filter on MDY and used that MDY filter on my dashboard. That way the user can only go back 7 days and see data from those specific days. No parameters used. That MDY dashboard filter is applied to the Daily Summary number but not the MTD Summary as I'm not sure what is needed there.

                 

                The today() was never used but would be used if I was simply trying to calculate the MTD data for the current month, in which case this question wouldn't be asked. If it was mid-month, the user would never see the previous month dates on the slider since it only goes back 7 days. But in the early month, if the latest date is before the 7th, it will go back into the previous month as well on the slider.

                 

                How do I account for this for the MTD view? For MTD view I want to see the MTD number based on whatever month the user has the MDY year filtered on. For example, let's say the MDY filter is filtered on Sept 27 and today's date is 30th - the daily summary shows only that day's data but the MTD will show Sept 1 - 30. If today was 29th, it would show Sept 1-29.

                 

                Another example, Sept 3 is selected on slider and current date is Sept 5. We'd see Sept 3 data for daily summary, but Sept 1-5 data for MTD. The user then scrolls back to August 31. We'd see data for August 31 under daily summary but MTD summary will show data from Aug 1-31.

                • 5. Re: MTD Filter based on date from a Dashboard Filter?
                  Joe Oppelt

                  Don't let proprietary data get in the way:

                   

                  Video demonstrates how to anonymize your workbook/data

                   

                  Here's the thing.  You know your application and goals and requirements inside and out.  Your description makes perfect sense to you.

                   

                  I'm coming in from the outside.  I can't wrap my head around the description.

                   

                  If I were doing something like (I think) you are describing, I would give the user a parameter to specify the target date, and then make all my calcs based on that parameter value.  You would be able to apply (or not) that parameter to various calcs and display whatever is necessary.