
1. Re: Single dimension member and window median on dual axis
swaroop.gantela Aug 1, 2018 6:18 PM (in response to Patrick Myles)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.

277571filt.twbx 15.9 KB


2. Re: Single dimension member and window median on dual axis
Patrick Myles Aug 1, 2018 6:51 PM (in response to swaroop.gantela)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?

277571filt_2.twbx 66.6 KB


3. Re: Single dimension member and window median on dual axis
swaroop.gantela Aug 1, 2018 7:49 PM (in response to Patrick Myles)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=MonthCopy11
and get growth from (Units1Units2)/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.

277571filt_3.twbx 60.8 KB


4. Re: Single dimension member and window median on dual axis
Patrick Myles Aug 1, 2018 8:56 PM (in response to swaroop.gantela)thank you very much for this. I'm going with your approach using parameters due to its relative simplicity. although it's not suboptimal 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 Aug 2, 2018 8:43 AM (in response to Patrick Myles)Patrick,
Apologies, I may have overcomplicated the selfjoin.
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_4.twbx 105.3 KB


6. Re: Single dimension member and window median on dual axis
Patrick Myles Aug 3, 2018 6:18 PM (in response to swaroop.gantela)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 Aug 3, 2018 8:24 PM (in response to Patrick Myles)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 Aug 4, 2018 12:20 AM (in response to swaroop.gantela)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 Aug 6, 2018 5:32 PM (in response to Patrick Myles)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.

277571filt5.twbx 57.1 KB


10. Re: Single dimension member and window median on dual axis
Patrick Myles Aug 6, 2018 8:36 PM (in response to swaroop.gantela)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..)

277571filt_3_set.twbx 56.2 KB


11. Re: Single dimension member and window median on dual axis
swaroop.gantela Aug 7, 2018 4:17 PM (in response to Patrick Myles)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.

277571filt_6.twbx 83.1 KB


12. Re: Single dimension member and window median on dual axis
Patrick Myles Aug 7, 2018 11:03 PM (in response to swaroop.gantela)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 Aug 8, 2018 9:32 AM (in response to Patrick Myles)Patrick,
I'm glad these have been helpful
All the best to you and your project.