6 Replies Latest reply on Jul 18, 2016 8:44 AM by brian.chu

    Bar graph with each mark requiring different date filter

    brian.chu

      I am trying to make a bar graph with 3 marks from the same measure but requiring 3 different date filters. The measure itself is an aggregate calculation ( i.e. sum(income) / sum(assets) ).

       

      1) All months (in the date filtered view)

      2) Most recent month (in the date filtered view)

      3) Previous month (in the date filtered view)

       

      Obviously using a global view won't work because (2) and (3) are single month specific.

       

      I've tried separate calculated fields for (2) and (3) using if / else statements and max / window_max date functions. However, I either get the aggregate / non-aggregate error or the previous / most recent month does not correspond to the dates selected in the global filter.

       

      I feel like this should be straightforward, but having some trouble here. Any help would be much appreciated. Thanks.

        • 1. Re: Bar graph with each mark requiring different date filter
          Joe Proulx

          I'm not sure I can answer your question, but I recently had to do something similar. The request was to provide one measure, summed, for the current report month, the prior report month, the current report month from the previous year, and a YTD sum.

           

          I was able to provide the current report month, the prior report month, and the current month in the previous year by filtering on "true" with this calculation:

           

          DATEDIFF('month',[REPORT_DATE],[prmReportMonth]) = 0 OR

          DATEDIFF('month',[REPORT_DATE],[prmReportMonth]) = 1 OR

          DATEDIFF('month',[REPORT_DATE],[prmReportMonth]) = 12

           

          The anchor month I used was a parameter (prmReportMonth), but you could easily use Today's date if you wanted to.

           

          What I ended up doing for YTD was creating that metric in a separate worksheet and merging them together in a dashboard. The result is this:

           

          example1.PNG

           

          Aside from the spacing, it looks like it's a single line of calcs. That said, my request wasn't to use a bar chart, so obviously your request has that added layer of difficulty.

           

          Joe

          1 of 1 people found this helpful
          • 2. Re: Bar graph with each mark requiring different date filter
            Russ Lyman

            You've got the right idea... you will need to create a calculated field for each, with the data filtering built into each calculation. If it needs to be dynamic, you could then tie each calculation into a parameter.

             

            Where are you getting the aggregation error? Can you post an example? If you're getting an aggregation error on a date function, you can sometimes get around the aggregation error by wrapping it in {}, and thereby turning it into an LOD calculation, which doesn't have the same aggregation restrictions that typical date functions are confined by.

            1 of 1 people found this helpful
            • 3. Re: Bar graph with each mark requiring different date filter
              John Sobczak

              You can create two new measures that are date bucketed of the main measure:

               

               

              Most recent month:

               

              if datetrunc('month', [date]) = datetrunc('month', today() then [main measure] end

               

              Previous Month

               

              if datetrunc('month', [date] = dateadd('month',-1,(datetrunc('month', today())) then [main measure] end

              1 of 1 people found this helpful
              • 4. Re: Bar graph with each mark requiring different date filter
                brian.chu

                Thanks Joe. I have a similar part of my dashboard that uses that same technique. Can't quite use it for all 3 measures on the same bar chart though.

                • 5. Re: Bar graph with each mark requiring different date filter
                  brian.chu

                  Thanks Russ. I'm still pretty new to Tableau and not very advanced in terms of LOD expressions or even parameters.

                   

                  I made a parameter called 'Most recent month' and then my first calculated field is:

                   

                  if DATEDIFF('month', [Date], [Most recent month]) = 0

                  then [Measure]

                  end

                   

                  Here I get the aggregation error. The only LOD wrapper that works is around the Measure (an aggregate measure), but that doesn't give me the correct figure.

                  • 6. Re: Bar graph with each mark requiring different date filter
                    brian.chu

                    Thanks John. I tried your solution but still get the aggregate error because my main measure is itself an aggregate value.

                     

                    Also, my reference date is not always today(), but I'm trying to get around that with a parameter (see my reply above to Russ).