1 of 1 people found this helpful
You can accomplish this by applying the chosen filter to worksheets. This is an option when you right click the desired filter in Sheet 2, and select "Apply to Worksheets". I have added this filter to Sheet 3 in the attached .twbx version 10.2.
Thanks for your solutions,
Unfortunately, i dont want to apply same filter to sheet 3, i only want to use the results from sheet 2 in sheet 3. i.e (customer numbers only in sheet2)
i already know about applying filters to multiple sheets.
Please bear in mind that sheet 2 already has 3 filters( Week end date, Group & Sales), i dont want to apply all these filters to sheet 3, only need the list of customers.
Any ideas please?
Thanks for that, it doesn't work has expected.
If i change the filter in sheet 2 and the number of customers change, it should change the number of customers in sheet 3 as well automatically.
in the workbook you sent, if you add a new week end dates or group in sheet2, the number of customers increase but it doesnt in sheet 3.
The list of customers in sheet 3 should be based on the result from sheet 2.
I am bit of confusion. If you want to transfer the cust no from sheet2 to sheet3, it has to be something related otherwise, it is not achievable.
The aim is to transfer the list of cust no from sheet 2 to sheet 3.
For example, in sheet 2, if i filter for Group A and Week end date 5th Nov 2010, it shows 1175 customer. What i want to do is now use those 1175 cust no in sheet 3 with the same weekend date, Group but no sales filter. (because i want to add other measures in sheet 3 without the sales restriction)
And if i change the date or Group in sheet 2, that should also update the cust no list in sheet 3.
hope this is much clearer.
I also notice in your solution that the filter on sheet3 is in grey, how did you do that and is there a reason for that?
It seems you want to create a subset of Customers based on criteria defined in Sheet 2, then take this subset to other sheets and apply set of different filters to it. This cannot be done using filters alone. One approach is to use a combination of parameters and sets. One limitation of parameters is that they don't have multi-selection option. Also, parameters are not dynamic. For example, a date parameter will not automatically update itself. You won't have a perfect solution in Tableau, but I can provide a working solution which can be further customized if you wish.
Step 1: Create parameter [Select Group]
This will be a multi-search parameter. We will use a regular expression calculated field to enable this feature.
Step 2: Create parameter [Sales Threshold]
You can modify the parameter options to suit your need.
Step 3: Create parameter [Start Date]
You need to decide what's best for you with the date parameters. Change to options as you wish. At the moment, Allowable values is set to 'Ranges'. This will give you the option to format the date parameter as a slider. However, I have it as 'Type In'. User will have to type the dates. This is mainly due to the peculiarity of your [Weekend End Date] field.
Step 4: Create parameter [End Date]
Same as [Start Date] above. The Start and End Date parameters will help you define Customer subset based on a range of Weekend End Dates.
Step 5: Create calculated field [Group Match?]
REGEXP_MATCH(MID([Group],FINDNTH([Group], " ", 1)+1, LEN([Group])-FINDNTH([Group], " ", 1)), REPLACE([Select Group], ",", "|")) OR
REGEXP_MATCH(MID([Group],FINDNTH([Group], " ", 1)+1, LEN([Group])-FINDNTH([Group], " ", 1)), REPLACE([Select Group], ", ", "|"))
This regular expression will help multiple group search. You can type in a single group name like A.
You can also search for multiple group names separated by comma or comma_space; like A,B,C or A, B, C
It must be in the format specified above. I don't know if the group names will always be single digits like A, B, C. However, I've made the formula robust to catch cases when group names are AF, GAA, etc.
Step 6: Create calculated field [Cust No Selection]
IF [Sales] <= [Sales Threshold] AND [Group Match?] AND [Week End Date] >= [Start Date] AND [Week End Date] <= [End Date] THEN [Cust No] END
Step 7: Create set [Customer Filtered]
This is created by right clicking on [Cust No] and then apply the condition shown in screenshot below.
Step 7: Add [Customer Filtered] set as a filter in Sheet 3. Right click and select 'Show In/Out of set'. Select 'In'. Then add the filter to context.
Remember that the number of customers in Sheet 3 may change if you apply further filters to the [Customer Filtered] subset. I can explain further but will allow you to play with the solution first.
Hope this helps.
Customer No Filter_OO.twbx 42.0 MB