13 Replies Latest reply on Aug 8, 2018 9:32 AM by swaroop.gantela

    Single dimension member and window median on dual axis

    Patrick Myles

      Hi all - I'm trying to achieve the following and need your help!

       

      Place an aggregated value (in this case % diff compare to previous value) on a dual axis axis with the median of the same aggregated value.  in my worksheet attached there is a median calculation which is the window median of product growth values.  now i want to put a single product growth in the same chart so it compare that products growth against the median.  but when i put a single product on the view it of course filters my window median too which i dont want.  any ideas?

       

        • 1. Re: Single dimension member and window median on dual axis
          swaroop.gantela

          Patrick,

           

          You can try the method described here:

          Filtering the View Without Filtering Underlying Data | Tableau Software

           

          It places LOOKUP(MAX(Product),0) on the Filter shift

          and does not affect the Window_Median.

           

          Please see workbook v2018.1 attached in the Forum Thread.

          1 of 1 people found this helpful
          • 2. Re: Single dimension member and window median on dual axis
            Patrick Myles

            Thank you very much .  this does work which is great! however... I noticed that now i'm unable to use the new filter (lookup max) on a dashboard apply to other worksheets.  If you see my attached now on the dashboard part, you'll see the new filter doesn't allow me to apply to the other worksheet (bar chart) in the view.  Do you know why this might be?

            • 3. Re: Single dimension member and window median on dual axis
              swaroop.gantela

              Patrick,

               

              I fear that is the case with a LookupFilter.

              I'm not sure of a way to account for that.

               

              One suboptimal workaround is to use a parameter

              of products and put on the filter shelf a calculated

              field of [Product Lookup]=[Product Parameter].

              Parameters have the drawback that they are not

              dynamic and we need to be recreated when a

              new Product is added.

              This is shown in the Dashboard of the attached.

               

              Another suboptimal approach is to join the dataset

              to itself on a Join field of 1 so that every month gets connected

              to every other month.

              So you would have MonthCopy1 and MonthCopy2.

              Then you could select the row where

              Product1=Product2

                and

              MonthCopy2=MonthCopy1-1

              and get growth from (Units1-Units2)/Units2

              This is shown in sheet 'sefjoin' of the attached.

              The Median growth would be trickier. Will need to look into that more.

               

              The drawbacks are:

              -because of the selfjoin, there would be multiple copies

              of Units and you would to account for this in your calculations

              using something like a MIN.

              -if your dataset is large, this join would detract from performance.

              1 of 1 people found this helpful
              • 4. Re: Single dimension member and window median on dual axis
                Patrick Myles

                thank you very much for this.  I'm going with your approach using parameters due to its relative simplicity.  although it's not sub-optimal as you say, it still does the job and works well.  the only thing is indeed it's less dynamic and i can't select multiple products.

                 

                thank you so much for your help here - it's been one of the most helpful i've had on this forum.  you are a star ! I

                • 5. Re: Single dimension member and window median on dual axis
                  swaroop.gantela

                  Patrick,

                   

                  Apologies, I may have overcomplicated the self-join.

                  I think that method may give you more functionality.

                   

                  The Self Join is a Left Join on Product and

                  MONTH([Date Copy1]) and MONTH(DATEADD('month',1,[Date Copy2]))

                  This way, there is only the adding of the copy2 columns

                  and no additional rows or duplicated values.

                  Join screenshot is below

                   

                  Now the percent difference can just be a standard

                  calculation and not a Table Calculation, so

                  we can use Level of Detail calculations to fix the amounts

                  regardless of filter selection.

                   

                  So Percent Growth becomes:

                  { FIXED [Product Copy1],[Date Copy1 (Months)]:

                  (SUM([Units Copy1])-SUM([Units Copy2]))

                  /SUM([Units Copy2])}

                   

                  and Median Growth becomes:

                  { FIXED [Date Copy1 (Months)]:MEDIAN([PercentGrowth2])}

                   

                  So now the filter is simply Productcopy1 and you can

                  select multiple products and use that across sheets.

                   

                  Please see workbook v2018.1 attached in the Forum Thread.

                   

                  277571filt.png

                  • 6. Re: Single dimension member and window median on dual axis
                    Patrick Myles

                    Thanks for this.  I have tried out this way too know.  it is very ingenious! Although i see it does allow more flexibility in one way (being able to apply all worksheets and not rely on parameters) the implications of having a self join are proving complicated for my dataset as it means i need to go and convert many other measures to MIN.  so weighing up the pros and cons, i think the parameter solution may still be better for me.  but i really find your solutions both extremely valuable and helpful.  I've also learnt a lot.

                    thanks again!

                    • 7. Re: Single dimension member and window median on dual axis
                      swaroop.gantela

                      Patrick,


                      Indeed, methods often can seem plausible in theory, but become impractical when implemented to scale.

                      This was an interesting question for me about wanting to get back to Level of Detail calculations

                      in a context normally reserved for Table Calculations. Makes me want to ponder the implications

                      of using a data scaffold to prevent data duplication. Hmm... more complications.

                      Anyway, glad it was helpful.

                      All the best.

                      • 8. Re: Single dimension member and window median on dual axis
                        Patrick Myles

                        hi again.  as mentioned i will need to go for the parameter option as the join option is proving quite complicated for me as my workbook is very big and many moving parts so requires quite a lot of added complexity. 

                        for the parameter option, the only thing i'm struggling with now is using a SET.  i want to use a set in the filter that is applied only to the median and not the product.  at the moment, if i put a set for of for example products with units over x, but only have that set impact the median.  would you be able to help?

                        • 9. Re: Single dimension member and window median on dual axis
                          swaroop.gantela

                          Patrick,

                           

                          This probably isn't what you're looking for because I veered away from sets and

                          just used a conditional statement.

                           

                          I didn't quite catch how you were making your sets, so I made a Total Units per Product field

                          WINDOW_SUM(SUM([Units]))

                          and created a parameter from that.

                           

                          Then I filtered the Growth this way:

                          IF [Total Units]>=[Total Units Parameter]

                          THEN [Growth] END

                           

                          Then the new Median Growth Filtered became:

                          WINDOW_MEDIAN([Growth Filtered])

                           

                          which seemed to be changing with new settings of

                          the Total Units Parameter, but I didn't validate the numbers.

                           

                          Please see workbook v2018.1 attached in the Forum Thread.

                          • 10. Re: Single dimension member and window median on dual axis
                            Patrick Myles

                            hi again.  I dont think this is quite what i had in mind.  Basically what i want is to create a set of the top N products by total units (see screen shot).

                            then when i put that top n set as a filter that affects the median calculation but doesnt filter out the actual product being selected (eg. if it doesn't happen to be in the top n). Does that make sense?  I"ve attached the workbook with the top N

                            i think it's complicated because of the parameter method i'm now using based on suggestion earlier (the self join method creates too many other issues for me unfortunately..)

                             

                            • 11. Re: Single dimension member and window median on dual axis
                              swaroop.gantela

                              Patrick,

                               

                              I apologize for the stepwise attempts,

                              but please see if the next version may have some ideas.

                               

                              Instead of using Sets, I used a Rank Calculated field:

                              RANK(SUM([Units]))

                               

                              Then I added another parameter, in this case Top N from 1 to 7.

                               

                              Then the check whether to include the Product's growth is:

                              IF [RankProducts]<=[Select Top N Parameter] THEN [Growth] END

                               

                              And the median growth of the just the top Ns becomes:

                              WINDOW_MEDIAN([Growth of Top Products])

                               

                              Please see workbook v2018.1 attached in the Forum Thread.

                              1 of 1 people found this helpful
                              • 12. Re: Single dimension member and window median on dual axis
                                Patrick Myles

                                wow, this is another great solution you found!  It works really well and was quite easy to implement.  all together you have solved my problem which is something i've been struggling with for many months!  I really appreciate your help and time and if you're ever in Melbourne let me know! Thank you!!!

                                • 13. Re: Single dimension member and window median on dual axis
                                  swaroop.gantela

                                  Patrick,

                                   

                                  I'm glad these have been helpful

                                  All the best to you and your project.