If Possible share your sample workbook
You can do something like this... note I added Sales to your data.
IF [Product Family] <> "M" AND [Product Family] <> "MX" THEN [Sales] ELSE NULL END
Does Product Family = M or MX? if Yes, do nothing, we don't want them.
If no, return the Sales for orders with these products.
Is that something like what you're trying to do?
172679.twbx 27.8 KB
We can perform it by using the "contains" function.
Returns true if the given string contains the specified substring.
CONTAINS(“Calculation”, “alcu”) = true
The following may help you, check it once.
Mark and Kumar,
thanx for the prompt responses, but I don't think either is going to work.
I need to include ANY product, as long as its PO number is the same as one that contains a QFX.
So i need to
1) generate a list of such PO numbers, and
2) do a lookup on every line to see if the PO matches ANY PO in the list generated.
Can that be done in Tableau?
1 of 1 people found this helpful
It seems Kumar and I both misinterpreted your question...
It maybe possible in Tableau but as you say its more of an Excel thing using vlookup or equivalent.
The other way is to do it in SQL, nest the results of query 1 inside query 2.
Tableau isn't really designed/ suitable for that kind of task.
I would suggest using Excel (or other) and then passing the results to Tableau for analysis.
Sorry, I'm sure its not the answer you wanted but its my best suggestion
That's a pity!
I can do it in Excel now, but we're moving the datasource online so then I will need everything done in Tableau.
Anyway thanx for considering my problem.
In that case once you have migrated, simply export the data out of Tableau into Excel and do the lookups as you have before. If you have Tableau Server you can automate the outputs to Excel to save you a job!
It maybe worth flagging this to someone as a potential issue ahead of your migration.
I always try and use the right product for the right task and in this case, Tableau is not the right product.
Who knows... someone may jump on this thread and blow everything i have said away, I hope for you someone can suggest a Tableau related workaround, id certainly be interested!!
2 of 2 people found this helpful
Create a calculated field, IsProductX "if product == 'product x' then 1 else 0 end'
Drag PO number to rows
Drag PO number to filter
Select Condition, By Field, Sum(IsProductX) > 0
Drag sales to columns
2 of 2 people found this helpful
I think the solution provided in Re: how to filter one item and view all matches does exactly what you ask for.
The steps for your data source are:
- Duplicate data source
- In secondary source
- Link on [Order Number] only
- Add [Product Family] to filters
- Select QFX and you get what your example shows
You get an annoying Null option in the filter. You can influence Tableau to improve (remove) this by up-voting Option to eliminate null value form Quick Filter on secondary data source field.
The attached workbook uses your spreadsheet.
Ps. It would be nicer if it wasn't necessary to duplicate (and blend) the data source and therefore think it would be convenient if Tableau supported correlated subqueries and multi-values parameters so this could be made with a calculated filter.
Attached Workbook Version: 8.2
both your proposals worked - I end up with a table that has all correct line items .
However now i need to create other tables and graphs (eg by region or by quarter) with this "QFX order" as a quickfilter - that i can toggle on and off.
Any ideas how to pull that off?
I tried creating a Set that has all PO numbers with IsProductX =1 (from Jade) and using that as quickfilter, but that didn't get me to the right amount...
I am not sure what your issue is in your other worksheets but guess it is related to the scope of the filter.
In the attached workbook, I duplicated Sheet 1, removed various pills, chose chart type Bar ... and changed scope of secondary source filter to All Using This Data Source.
Check also if linked fields (Order Number linked, others not) in secondary source are unchanged.
I hope this helps.
Attached Workbook Version: 8.1
I really appreciate your support.
I'm new to Tableau (now 1 week) and on a steep learning curve - which is fun but annoying.
Apologies but i am not able to open the workbooks you attached.
I am on version 8.1 - i was told to downgrade from 9 as our server is on 8.1 and apparently won't allow uploads from newer versions. It seems Tableau is unusally picky on versions, and I haven't figured out how to open newer version files...
I tried to DM you but it seems the site doesn't support that (or you prefer not to be DMed)...
Welcome to the forum, Gijs
I have replaced the 8.2 workbook in my previous comment with a re-created 8.1 version and hope it does what you need.
I have 9.0 installed but use 8.2 for answering questions where version is unknown so questioners are able to open the attached workbook. In this case it was obviously not enough. I could have asked for your version before doing anything else, but didn't, because I think it is
- too negative (feelings) for the questioner
- too committing (work) for the answerer
As for reason 2 I generally prefer to make a solution before possibly answering and thus lowering any potential future work commitment.
Here are some previously expressed frustrations about this issue:
Hopefully tableau.community is able to find a good solution for this so the community experience becomes even more pleasant and productive for both questioners and answerers.