7 Replies Latest reply on Nov 18, 2018 2:32 PM by Jennifer VonHagel

    Calculations With Dates Outside View Filter

    Brandon Scott



      I've got a question that I havn't been able to get an answer to or solve myself so here I am.  As the title suggests, I'm trying to do calculations based on a date field that has a filter for the view, but the calculation contains dates outside that filter.


      One Dimension - [Transaction Date]           LOD: yyyy-mm-dd hh:mm:ss

      One Measure - [Transaction Amount]         LOD: $xxxx.xx


      Here's what I am attempting.

      A dual axis dashboard:

      One axis is a vertical bar chart of the sum of [Transaction Amount] over the last 14 days.

      The second axis is a gantt chart overlay (or cell reference lines if more applicable) of the average sum of transactions for each day of the weak, of the previous quarter.



      Calc Fields:

      Number of Weekdays:

      {FIXED DATENAME('weekday', [Transaction Date]), DATE([Transaction Date]): COUNTD(DATENAME('weekday',[Transaction Date]))}


      This finds the number of weekdays broken by current dimension in view, but I need it to be the number of each weekday in the previous quarter, not the current view.



      Previous Quarter Bool:

      IF DATEPART('quarter', [Transaction Date]) = DATEPART('quarter', TODAY()) - 1

          AND DATEDIFF('month', [Transaction Date], TODAY()) <= 6

              THEN TRUE

           ELSE FALSE



      I know I need something along the lines of:

      {FIXED DATENAME('weekday', [Transaction Date]) : SUM([Transaction Amount]/SUM([Number of Weekdays])}


      However, similar to the issue with [Number of Weekdays], I can't get it to work based off last quarter's numbers, just the last 14 days numbers in the view from filter.


      Thanks ahead of time,


        • 1. Re: Calculations With Dates Outside View Filter



          Please disregard my previous reply, I think there is a simpler way:

          Tableau Tip Tuesday: Using LODs to View the Latest, Previous and Prior Months


          Using that, Previous Quarter sales  are:

          IF DATEDIFF('quarter',DATETRUNC('quarter',[Order Date]),DATETRUNC('quarter',TODAY()))=1

          THEN [Sales]



          Fixing to day of the week:

          { FIXED DATENAME('weekday',[Order Date]):AVG(

          { FIXED [Order Date]:SUM([Previous Quarter Sales])}



          This can be plotted as a dual axis.

          Please see Sheet10 of the workbook v10.2 attached in the Forum Thread:

          Calculations With Dates Outside View Filter


          • 2. Re: Calculations With Dates Outside View Filter
            Paul Wachtler

            Hey Brandon,


            If every day in your dataset had data, this wouldn't be too hard to do with a Lookup calc.  However there are some days that are a quarter before your current period that have no data and are null in the dataset.  Specifically August 8th and 9th - so on November 8th and 9th, there shouldn't be any ref. line there.


            I think you may need to left join your dataset against a dataset of only dates, that has every date possible between the start and end of your dataset.  That way, for days without values, it would still count it as a date, and we could calculate the value for the previous quarter.  Right now your dataset doesn't know that 8/8 and 8/9 are actual days.


            Screen Shot 2018-11-16 at 8.40.45 PM.png




            • 3. Re: Calculations With Dates Outside View Filter
              Jennifer VonHagel

              Hi Brandon,


              See if this works for you.  There are four tabs in the attached workbook, as shown in the screenshot below. In these I build and check the calculations, with the last tab showing the chart put together.  I put the days of the week as a color legend here only because I wanted to click and highlight a given weekday's Prior Qtr Avg in order to double-check it against the previous tabs' value.



              The calculations should be self-explanatory, as I can see you grasped the concepts in your question. But of course I am happy to explain further if the info in the workbook isn't clear .




              1 of 1 people found this helpful
              • 4. Re: Calculations With Dates Outside View Filter
                Paul Wachtler

                Put it together for you.  The attached excel spreadsheet is what I used to fill in all the days.  Updated Tableau file is attached too.




                Screen Shot 2018-11-16 at 9.26.32 PM.png

                • 5. Re: Calculations With Dates Outside View Filter
                  Brandon Scott

                  This is beautiful in its simplicity.  I don't think I would have gotten to this point on my own.  I definitely don't have any experience in nested LODs yet.  Thank you so much for your assistance.  Everything looks in order, but I still need to test it on my real dataset.


                  Thanks again.

                  • 6. Re: Calculations With Dates Outside View Filter
                    Brandon Scott

                    Thanks for your help on this!  I think this is the direction that I was going in, but wasn't able to make it this far.  I'd mark this answer as correct as well if I could.  I think Jennifer's is a little cleaner though.

                    • 7. Re: Calculations With Dates Outside View Filter
                      Jennifer VonHagel

                      Glad it helped!


                      It seems clear and simple when it's done, but I did have to fiddle with it a bit to get it there , which I why I'm careful to check pieces of the calculation as I go.  As for the nest, to be sure it's clear:



                         { FIXED DATENAME('weekday',[Order Date]) :  

                              AVG({ FIXED [Order Date] : SUM(IF [Is Prior Quarter] THEN [Sales] END) })




                      The inner fixed calc finds the sum of sales by date, only for dates in the Prior Quarter range.

                      The outer fixed calc then averages these sums as grouped into weekdays (Sat, Sun, etc).

                      Wrapping it in ATTR() just ensures that the values, which will be repeated on multiple records, do not get SUM()'d when used in a view. Also, if the calculation were used in a wrong way (with a dimension that isn't weekday), it could return * rather than a number which will alert you that there is a problem.