9 Replies Latest reply on Mar 12, 2018 3:07 AM by Yuriy Fal

    Running Average

    Emily Z

      Hi,

       

      I'm creating a dashboard and what I want to achieve is that: when I click on the "month" in sheet 1, sheet 2 will only show the selected month with the same number as in sheet 1.

      The problem is, I use running average for both sheets, and in sheet 2 it will only show the number (in stead of running average, since there's only one month selected) in the selected month. Is there any way to achieve this?

       

      Attached the sample file for your kind reference. Thanks!

       

      Emily

        • 1. Re: Running Average
          Łukasz Majewski

          Interesting problem which I think cannot be resolved with action filter. You would have to use Table Calculation Filter for months but it cannot be targeted with an action.

          If it was not running average but total/overall then you could use LoD expressions and standard filters. But I cannot see a way around for running aggregation

          • 2. Re: Running Average
            Akram Ebrahim

            Hi Emily,

             

            It's difficult to filter data on running average table calculations as per previous comment.

            Alternate way is we can achieve this by using highlight filter action on dashboard.

            1 of 1 people found this helpful
            • 3. Re: Running Average
              Emily Z

              Thanks Majewski!

               

              Yes I tried table calculation filter previously, but it doesn't look as nice as action in terms of displaying my actual dashboard. Thanks for the suggestion anyway!

               

              Emily

              • 4. Re: Running Average
                Emily Z

                Hi Akram,

                 

                The actual dashboard is much more complicated than the sample I attached thus highlight is not the desired outcome. Thanks for your suggestion!!

                 

                Emily

                • 5. Re: Running Average
                  Łukasz Majewski

                  There is one more option: Filtering Views using URL Parameters | Tableau Software

                  But the downfall of this approach is that it always opens a new window / tab at first use so it sounds like a good option for a "landing page" from which users navigate elsewhere.

                   

                  Nonetheless here is how it works:

                   

                  I created a table calculation as [MonthT] (yes, this is what cannot be done with a filter action ):

                   

                  The IF statement is just to assert the months are yielded only in Sheet2 where you have [Dept] in Details.

                   

                  In Sheet2 it is placed next to regular [Month] and addressed like this:

                   

                  while in Sheet1 it is in Details shelf and addressed using 'Cell':

                  when we pull out the quick filter it only has NULL in values and now the trick here is to set it as exclusive filter:

                  This is to avoid filtering Months in Sheet1 by the same URL action. And the actions are defined as follows:

                   

                  1. When published to Tableau Server:

                   

                  2. For Tableau Public:

                   

                  Here you may test the Public version: | Tableau Public

                  • 6. Re: Running Average
                    Emily Z

                    Thanks a lot Majewski for your detailed reply!

                     

                    I tried but couldn't get the desired outcome --- number shown in the selected month is not the running average but the number for that month only. Is it because I'm using Tableau Desktop?

                     

                    My actual workbook needs to show 2 bars in sheet 2, one for current year, one for last year. And when I clear the selection, all bars will be excluded.

                    • 7. Re: Running Average
                      Łukasz Majewski

                      Then it is not a solution in your case. I shared it here as I realized it allows to effectively set a filter based on table calculation which is one aspect of what would be a solution if this was allowed in filter actions. It is actually appalling that it is not.

                       

                      I now shared something that works as you would expect but with this sample only. It is based on "cascaded" LoD fields which is rather cumbersome and in case of many partitions might not be feasible at all (2x16 nested LoD is max I guess).

                       

                      Regards,

                      Łukasz

                      1 of 1 people found this helpful
                      • 8. Re: Running Average
                        Emily Z

                        Thanks Majewski! Although this is a bit cumbersome, I really appreicate your sharing as I learned something that I did not know before.

                        • 9. Re: Running Average
                          Yuriy Fal

                          There is a possible workaround,

                          which would require some data re-shaping

                          (joining the main table with the calendar one).

                           

                          Please find the attached.

                           

                          Yours,

                          Yuri