9 Replies Latest reply on Sep 18, 2017 9:43 AM by Michael Simons

    Relative Dates Filters as Dimension

    Michael Simons

      Hi all.

       

      Looking for help on showing relative date periods as rows as below:

       

      Screenshot 2017-09-06 23.01.48.png

       

      The data is updated every hour so TODAY() will probably be used somewhere. As you can see in the example as well, I'm trying to split out my mouldy and my fresh produce which have their own Sales$ measure.

       

      For clarity, by this week I don't mean "the last 7 days" I mean "Week 36" (Sunday to Saturday Week). Same for the other periods, I'm not looking for TODAY()-7 for example.

       

      Time periods in Tableau courtesy of Mark Fraser comes very close but I cant seem to get it working.

       

      Thanks in advance

       

      Cheers

        • 1. Re: Relative Dates Filters as Dimension
          Jennifer VonHagel

          Hi Micahel, Not sure if you have Tableau 10.3 so I will put in some screenshots.

           

          How is your source data set up?  Is it like this - where Sales is its own column and everything else is a dimension?

           

          Date

          Fruit (apples, pears, bananas)

          Fruit Status (Mouldy, Fresh)

          Sales: $$

           

          Or are there multiple sales fields, like:

           

          Date

          Fruit (apples, pears, bananas)

          Mouldy Sales: $$

          Fresh Sales: $$

           

          Hopefully it is the first option. If so, we can easily create measures for all these time periods: The workbook attached has the measures, has some sheets to check they are bringing back the values we hope they do , and a Table sheet that puts the measures in the format you have listed above.  I'll write out the list of formulas for reference, then show screenshots.

           

          Sales Today: IF [Order Date] = TODAY() THEN [Sales] END

          Sales Yesterday: IF [Order Date] = TODAY()-1 THEN [Sales] END

          Sales This Week: IF DATEDIFF('week',[Order Date],TODAY()) = 0 THEN [Sales] END

          Sales Last Week: IF DATEDIFF('week',[Order Date],TODAY()) = 1 THEN [Sales] END

          Sales Two Weeks Ago: IF DATEDIFF('week',[Order Date],TODAY()) = 2 THEN [Sales] END

          Sales This Month: IF DATEDIFF('month',[Order Date],TODAY()) = 0 THEN [Sales] END

          Sales Last Month: IF DATEDIFF('month',[Order Date],TODAY()) = 1 THEN [Sales] END

          Sales Two Months Ago: IF DATEDIFF('month',[Order Date],TODAY()) = 2 THEN [Sales] END

           

           

          Here's the sheet that shows the Month measures.

          Sheet to check Week Measues:

          Here's the sheet that shows the Day measures. Can you believe this data does not have any orders for TODAY, 9/6!

          And a table putting it together:

          1 of 1 people found this helpful
          • 2. Re: Relative Dates Filters as Dimension
            Bill Lyons

            Your question and example lacks many specifics, but looking at your example, it appears that what qualifies as "today" also qualifies as "this week" and "this month." Is that your intent? If so, Tableau's default sheets will not do that because by definition, the same measure value cannot be a member of multiple dimension values within the same view (sheet).

             

            However, that does not mean there is not a way to simulate it using a dashboard. In the attached sample, I created 3 calculated fields to handle days, weeks and months, as shown in your screen shot. These are each used in their own sheet, and the three sheets are combined into a dashboard.

             

            You do not specify the criteria for determining "fresh" or "mouldy," so I have ignored those for my example. And, lacking an example data set, I have used the Tableau Superstore database. If my example does not replicate your problem, please see Packaged workbooks: when, why, how and include an appropriate example.

             

            I hope that helps.

            1 of 1 people found this helpful
            • 3. Re: Relative Dates Filters as Dimension
              Michael Simons

              jennifer.vonhagel this is absolutely on the money. Excellent excellent stuff - much easier than I thought it would be actually.

               

              The only tweak I had to make was that my Date field was DATE & TIME and therefore TODAY and YESTERDAY were coming up blank. So I had to duplicate the field and set it to date only. I'm sure theres a fix to make your calcs work (maybe adding 1 second to the times? As they default to midnight on the day in question).

               

              Anywho, thank you so much for the quick response. Now to find a way to incorporate two different sales columns.

               

              Data is set up as follows:

               

              Data Source 1:

              Date

              Product (Apples, Pears, Bananas)

              Mouldy Sales

               

              Data Source 2

              Date

              Product (Apples, Pears, Bananas)

              Fresh Sales

               

              Thanks!!

              • 4. Re: Relative Dates Filters as Dimension
                Michael Simons

                Hi Bill

                 

                Thanks for your reply.

                 

                This comes close but Jennifer comes closer in that its all in one sheet. FYI my data set up is as follows:

                 

                Data Source 1:

                Date

                Product (Apples, Pears, Bananas)

                Mouldy Sales

                 

                Data Source 2

                Date

                Product (Apples, Pears, Bananas)

                Fresh Sales

                 

                I havent attached sample data as the fields are all fictitious and are an illustrative example of my data which I am unable to share.

                 

                Cheers

                1 of 1 people found this helpful
                • 5. Re: Relative Dates Filters as Dimension
                  Michael Simons

                  And to throw one more spanner in the works.

                   

                  What if we wanted to supercharge this and a some more rows to the table?

                   

                  Best Week Ever (by sales total)

                  Worst Week Ever (by sales total)

                   

                  Best Month Ever (by sales total)

                  Worst Month Ever (by sales total)

                   

                  Best Day Ever (by sales total)

                  Worst Day Ever (by sales total)

                   

                  Average Daily Sales (average of all time sales per day)

                  Average Weekly Sales (average of all time sales per day)

                  Average Monthly Sales (average of all time sales per day)

                  • 6. Re: Relative Dates Filters as Dimension
                    Jennifer VonHagel

                    Hi Michael, glad it helped!

                     

                    Those new measures are all easily do-able, I can write the syntax up a few hours from now. For the Best/Worst I'll create sets that look for the Top or Bottom 1 Day/Week/Month by Sales (That's 6 different sets). Then we write a formula saying if Set Best Month Ever is true then sales end, similar to the other formulas.  For average weekly/monthly, I'll use a FIXED formula to first sum sales by week/month, then average across weeks/months.

                     

                    Thinking ahead a bit, I bet you'd also want to show the date of the Best/Worst Day/Week/Month. Putting those dates in the table will not (I think, haven't fiddled around with yet) come out nicely, because we're dicing everything by dimensions. Your best option there would be to trick it out in a dashboard. Put your table in with space for dates, and then create floating dating fields that you can fit on top of it.

                     

                    Is your data in a database or in spreadsheets? If the structure of the data is as simple as you've shown and these are in spreadsheets, you could union the sheets in Tableau. I have a feeling union doesn't work for connections to databases, but if you have a database you could hopefully get the data into good shape before bringing it to Tableau.

                     

                    Best,

                    Jennifer

                    1 of 1 people found this helpful
                    • 7. Re: Relative Dates Filters as Dimension
                      Michael Simons

                      Hi jennifer.vonhagel

                       

                      Thanks for your reply. Thanks for offering to write up the syntax for me. I'm going to look into sets now as its not something I've used before.

                       

                      In answer to your questions:

                      - I dont need to know which was the best week/day/month (can reference to that on other reports)

                      - my data is all in various databases unfortunately and I cant modify any data there (so I'm having to create a different set of fields for each data source)

                       

                      Finally, do you think LOD calcs will work with your formulas? I am playing around and trying to see if I can find the:

                       

                      HOW MANY CUSTOMERS MADE THEIR FIRST ORDER IN EACH PERIOD (this week, last week etc etc)

                       

                      Everything I keep trying bugs outs!

                      • 8. Re: Relative Dates Filters as Dimension
                        Jennifer VonHagel

                        Hmm, finding best/worst can play out in a couple ways. Which are you going for? I've turned on Subtotals and Grand totals to better understand how the formulas interact with the way the table is set up.

                         

                        In Best Month Ever below, the formula finds the highest TOTAL monthly sales ever (summed across all segments and categories and everything), which was 118k. It then breaks this 118k out into each Segment and category. The segment/categories add up to our banner month's 118k, but are not necessarily the highest sales that particular segment and category has seen.

                         

                        In Max Monthly Sales, the calculation finds the highest monthly sales in each segment/category. These could potentially all be from completely different months. The subtotals and Grand total here are finding the max value of the row level values listed.

                         

                        To get Best Month Ever, I used Sets. I can explain more on that later if you want to go this way.

                         

                        To get Max Monthly Sales, I created a LOD Monthly Sales measure. This creates sums of sales for each month.

                        Monthly Sales: { INCLUDE DATETRUNC('month',[Order Date]) : sum([Sales]) }

                        • The DATETRUNC gives me the year/month of each order date. You can use this with week, day, etc. For instance, you said your dates had times. You could do the function DATETRUNC('day',[YOURDATETIME]) to change your datetime to a date on the fly.
                        • In the LOD we could use either FIXED or INCLUDE. FIXED would give us the total sales by month regardless of any dimension we put in the view. INCLUDE gives us monthly sales, but allow the sales to also be shown by whatever dimensions we put in the view. In this case, Segment and Category.

                         

                        I can now put this Monthly Sales measure on the Measure Values shelf 3 times, and then simply choose Minimum, Maximum and Average to get your Worst, Best, and Monthly Sales.

                        • 9. Re: Relative Dates Filters as Dimension
                          Michael Simons

                          Hi jennifer.vonhagel

                           

                          Absolutely nailed it. Thank you so much. I'm a little annoyed I didn't think to use MIN, MAX and AVG. So simple - I guess I was trying to think of something complex so missed the obvious!

                           

                          Thanks very much for your help - your detailed responses were great and are a perfect example of why these forums are one of the biggest selling points to Tableau.