Thanks Andy this is brilliant and so timely as i was working on the very same issue this week but having to do it all via excel, this has saved me hours of work. thankyou
Thank you very much! As always explained by you super simple and reproducible at any time. Now all we need to do is changing the column header flexibly.
Andy this is terrific and easy to follow. My question however is this: can the filter you created using the window_min function be connected some way to the primary dimension you are filtering in order to filter on other sheets?
hi this video is very informative but just wanted to check how i would be able to apply this filter (in this video this would be the selected dimension) across worksheets (using the same filter across worksheets)
Andy - this is very helpful. Thank you. I am curious if there is a way to do this when you have duplicate values in the dimension you are performing the "LOOKUP(MAX" function on? I am reporting Sales by Sales Manager/Store, where there are sometimes multiple managers in a single store. Trying to filter on the store without losing the overall ranks. Your example works... except where I have multiple managers at one store. I heard you say in the video that this example works because you have no duplicate regions. Is there a simple tweak to overcome this use case issue?
Try creating a calc that concatenates your two fields. Add that to the detail shelf and then compute the table calc by that field since those will no be unique.
Thanks Andy. On dashboard, the ranking reset to 1 for any dimension after applying filter to all worksheet using the database. Finding this challenging. I would appreciate if you could help.
Thank you for this tutorial! Really Great! How can I maintain the Segment sorting for East Region only? I am having trouble with multiple dimension filtering :)
Great Video Andy, Thanks! The CASE Function does not work on created calculated measures such as Margin % for changing the parameter measure towards the end of your video. What function would you use for these? Thank you
Is there a way to use the Rank Filter to filter the selected option across multiple sheets for a dashboard? The rank filter does not have an option to apply to other sheets.
@@tylerturb table calc filters only work on a single worksheet due to the order of operations. A table calc filter happens after the view is drawn in the worksheet.
Hey Andy, this has been extremely useful. Thank you so much. Could you also tell me if the filter that you created using window function can be translated into a global filter?
This was very useful, thankyou! I have a quick question though.I was trying to use this rank filter across multiple sheets, unable to work that ways, can you help?
Thanks Andy really helpful video! I have a question to expand this tip that I'm stuck with, and wondering if you or someone from the community could help. I have Publisher websites, and want to see the top 10 of these, and then their top 5 performing sub-industries (e.g. clothing, credit cards, hotels). How would you still have the visibility of all sub-industries when you've already filtered to top 10 publishers? In your example it would be top 2 regions first and then sub-categories of these (but not filtering out ones that are removed via the top 2 regions first). I tried context filters, but I'm having some issues as my performance metric is a quality score, that already uses a bunch of FIXED LODs that benchmark Publisher vs. the industry. Sorry hard to explain here, but any tips to preserve one dimension, when filtered out by another would be amazing :) Thanks!
Hmmm that’s a bit tricky. I’m sure it’s possible but it would require you to still have all of the values in the view. Instead of filtering out those in the top N, you would need to hide those out of the top N. This would keep them available for the top N. You will need to duplicate the dimension to use it twice.
how can I use this in a dashboard? I want to be able to filter my dashboard based on this filter? If i use my normal region filter the rank changes but with this filter it holds but i cant add to control my dashboard.
Hey there - I see you are using a cube. I'm having difficulty with mine. My data source is Microsoft Analysis Services cube. I was able to follow along up to Rank Filter - for some reason tableau doesn't allow me to use a dimension name in a calculated field. And when you created the Rank calculated field SUM wasn't an option, just window_sum or running_sum. Any chance you have videos on how to do the stuff you did here for this type of data source?
I have 3 measures in the view. I want to give Rank = 1 to the highest value in each measure. For example, Measure-1 has values 2000, 2020, 500, Measure-2 has values 3000, 2000, 400 and meausre-3 has values 2500, 1000, 100. So My rank should be 1 for 2020 of measure-1, 1 for 3000 of measure-2, 1 for 2500 of measure -3 etc. I want to create single rank for all the measures. Is this possible?
We have certain reports created using SQL server and some are using Oracle as data sources. Our data sources gets refreshed at random schedules. Let's say databases refresh completes by early morning 7am and we scheduled reports update to start at 12pm using normal schedule processing. There is a gap of more than 5 hours between database refresh and reports refresh. User wants the updated reports soon after database refresh. They want a mechanism like once database refresh completes it has to trigger resports schedule automatically so that they need not to wait so much time (like 5 hours in normal scheduling). Is there any way to achieve this. Note: Database refresh completes sometimes at 7am, sometimes at 8am etc. It's not fixed one.
Even 5 years later this still a masterpiece Thanks Anddy
Thanks Andy this is brilliant and so timely as i was working on the very same issue this week but having to do it all via excel, this has saved me hours of work. thankyou
Thank you, Andy! You helped me a lot with this tip.
thank you!!!! The other solution I had seen in the internet used "LOOKUP" function and didn't work. This worked perfectly for what I needed!
Excellent!
Thanks Andy you are the best
Thank you very much! As always explained by you super simple and reproducible at any time. Now all we need to do is changing the column header flexibly.
Thanks Michael! The header is exactly what I was thinking about. Might be better to simply hide the headers instead.
Thanks for the tip Andy, pretty cool!
Andy this is terrific and easy to follow. My question however is this: can the filter you created using the window_min function be connected some way to the primary dimension you are filtering in order to filter on other sheets?
hi this video is very informative but just wanted to check how i would be able to apply this filter (in this video this would be the selected dimension) across worksheets (using the same filter across worksheets)
Andy - this is very helpful. Thank you. I am curious if there is a way to do this when you have duplicate values in the dimension you are performing the "LOOKUP(MAX" function on? I am reporting Sales by Sales Manager/Store, where there are sometimes multiple managers in a single store. Trying to filter on the store without losing the overall ranks. Your example works... except where I have multiple managers at one store. I heard you say in the video that this example works because you have no duplicate regions. Is there a simple tweak to overcome this use case issue?
Try creating a calc that concatenates your two fields. Add that to the detail shelf and then compute the table calc by that field since those will no be unique.
Thanks Andy. On dashboard, the ranking reset to 1 for any dimension after applying filter to all worksheet using the database. Finding this challenging. I would appreciate if you could help.
That expected since you’re using a dimension filter. Those occur before the rank calc in the order of operations.
@@vizwiz Thanks for your response. Could you suggest a way around this.I need to keep the ranking.
Thank you for this tutorial! Really Great! How can I maintain the Segment sorting for East Region only? I am having trouble with multiple dimension filtering :)
Great Video Andy, Thanks!
The CASE Function does not work on created calculated measures such as Margin % for changing the parameter measure towards the end of your video. What function would you use for these?
Thank you
Is there a way to use the Rank Filter to filter the selected option across multiple sheets for a dashboard? The rank filter does not have an option to apply to other sheets.
Case only works with dimensions. Use an IF instead.
@@tylerturb table calc filters only work on a single worksheet due to the order of operations. A table calc filter happens after the view is drawn in the worksheet.
Awesome Tutorial Andy... my only wish in life is to become atleast 50% like you in tableau.
Thank you Andy for this tutorial. I was wondering if there is a way to get the minimum of hte rank after rank filter
Possibly the window_min function or another rank that is ascending.
Thanks for sharing! It's a very nice tip.
Hey Andy, this has been extremely useful. Thank you so much. Could you also tell me if the filter that you created using window function can be translated into a global filter?
No it cannot as it’s a table calc filter. Only dimension filters can be made global.
This was very useful, thankyou! I have a quick question though.I was trying to use this rank filter across multiple sheets, unable to work that ways, can you help?
Since rank is a table calculation, it cannot be applied to multiple sheets. It could probably be done with a parameter though. I’ll have a look.
Here's a video I created today. th-cam.com/video/-38wLUWohqQ/w-d-xo.html
Hi Andy, thanks for the tutorial! Would this be possible on multiple filters and more than one dimension?
Since it’s based on a table calc, yes.
Thanks Andy really helpful video! I have a question to expand this tip that I'm stuck with, and wondering if you or someone from the community could help. I have Publisher websites, and want to see the top 10 of these, and then their top 5 performing sub-industries (e.g. clothing, credit cards, hotels). How would you still have the visibility of all sub-industries when you've already filtered to top 10 publishers? In your example it would be top 2 regions first and then sub-categories of these (but not filtering out ones that are removed via the top 2 regions first). I tried context filters, but I'm having some issues as my performance metric is a quality score, that already uses a bunch of FIXED LODs that benchmark Publisher vs. the industry. Sorry hard to explain here, but any tips to preserve one dimension, when filtered out by another would be amazing :) Thanks!
Hmmm that’s a bit tricky. I’m sure it’s possible but it would require you to still have all of the values in the view. Instead of filtering out those in the top N, you would need to hide those out of the top N. This would keep them available for the top N. You will need to duplicate the dimension to use it twice.
Hi Andy, please make a video on MTD, QTD, YTD and MoM, QoQ, YoY calculations
There’s a video already. Search my blog to find it.
Thanks Andy.
Hi Andy thanks a lot for this.
Just wanted to know if we can design a Cascading Action filter
Search the forums. You may find it there. Yes, it's possible, has nothing to do with this tip though.
how can I use this in a dashboard? I want to be able to filter my dashboard based on this filter? If i use my normal region filter the rank changes but with this filter it holds but i cant add to control my dashboard.
Hey there - I see you are using a cube. I'm having difficulty with mine. My data source is Microsoft Analysis Services cube. I was able to follow along up to Rank Filter - for some reason tableau doesn't allow me to use a dimension name in a calculated field. And when you created the Rank calculated field SUM wasn't an option, just window_sum or running_sum. Any chance you have videos on how to do the stuff you did here for this type of data source?
I’m not using a cube. Cubes have very limited functionality with Calcs in Tableau because they have to be precomputed in the cube.
THANK YOU!!!
I have 3 measures in the view. I want to give Rank = 1 to the highest value in each measure. For example, Measure-1 has values 2000, 2020, 500,
Measure-2 has values 3000, 2000, 400 and meausre-3 has values 2500, 1000, 100. So My rank should be 1 for 2020 of measure-1, 1 for 3000 of measure-2, 1 for 2500 of measure -3 etc. I want to create single rank for all the measures. Is this possible?
Wouldn’t you just add them all together and then rank that value?
Loved it
We have certain reports created using SQL server and some are using Oracle as data sources. Our data sources gets refreshed at random schedules. Let's say databases refresh completes by early morning 7am and we scheduled reports update to start at 12pm using normal schedule processing. There is a gap of more than 5 hours between database refresh and reports refresh. User wants the updated reports soon after database refresh. They want a mechanism like once database refresh completes it has to trigger resports schedule automatically so that they need not to wait so much time (like 5 hours in normal scheduling). Is there any way to achieve this.
Note: Database refresh completes sometimes at 7am, sometimes at 8am etc. It's not fixed one.
How to create rank filter based on multiple dimensions in Rows (i.e. Range & Sub-Category)?
Do you want the top X within each range?
@@vizwiz I want top x within region and sub-category dimension at same cut
@@SSM938 so the top X sub-categories within each region?
I have a question, what if we need the filter for another chart on another sheet, can we apply filter to selected sheet like regular filter?
A table calc filter cannot be applied to multiple sheets. This is due to the order of operations and when table calc filters are applied.
Linda, see the response above. Thanks.
it terrific but there is an issue we cannot apply this filter to different worksheets in a dashboard
Correct, table calc filters only apply to one sheet.
Andy, Is it possible to do a Rank of my competitors but always keep me in the rank?
Bill James as long as your company name is in the same dimension, sure you can.
Great tip
this worked great until I tried to pass the RANK FILTER as an action filter. It just will not show up as a match for the target sheet unfortunately
Correct. You can’t use table calcs in the actions.
@@vizwiz was able to work around it with using set actions. That way the calculations remain in the background because I’m not filtering ahead of it
@@Orholam5 excellent...good work!
I dig it!