-
1. Re: Calculated Field to Pull Information from Two Different Fields
Sreekanth Kasaraneni Oct 19, 2016 11:20 AM (in response to Josh Delekta)Hi Josh,
i assume you need to use parameters as filters, please check the link below.
Filtering Multiple Dimensions Using Parameters | Tableau Software
Thanks,
Sreekanth.
-
2. Re: Calculated Field to Pull Information from Two Different Fields
Josh Delekta Oct 19, 2016 11:39 AM (in response to Sreekanth Kasaraneni)Sreekanth
Thank you for following up. I don't follow this application example. I've used parameters before to filter across multiple sheets in a dashboard but not to get to an end number.
What would you recommend the settings for the parameter and what would the calculated field be read as?
-
3. Re: Calculated Field to Pull Information from Two Different Fields
Kaz Shakir Oct 19, 2016 11:44 AM (in response to Sreekanth Kasaraneni)I was thinking along the same lines as Sreekanth. I'm assuming that your data looks something like this:
If so, then I would create a parameter for the categories like this:
And then create a calculated field called LevelToShow:
LevelToShow
CASE [DetailLevel]
WHEN "Supplier" THEN [Supplier]
WHEN "Business Unit" THEN [Business Unit]
WHEN "Operating Expense" THEN [Operating Expense]
END
And then create a viz that might look something like the below picture, and use the DetailLevel selector to change which grouping you want to see the results for.
-
4. Re: Calculated Field to Pull Information from Two Different Fields
John Croft Oct 19, 2016 11:59 AM (in response to Josh Delekta)Not sure if this answers your question. I tried to mock up a dummy data set based on your initial post. I've attached the workbook as well.
-
Spend Workbook.twbx 9.7 KB
-
-
5. Re: Calculated Field to Pull Information from Two Different Fields
Josh Delekta Oct 19, 2016 1:36 PM (in response to John Croft)Thank you everybody for the quick responses. I'm working on trying the example with parameters.
John,
The calculation gave me worked but its only giving me the sum when the supplier has spend that hits both the BU and the Operating expense. I'm trying to total everything from BU and Operating Expense together but also make sure I'm not double counting the amount.
Another slightly different example, when BU = BU 1 I want to sum the spend with when OE = OE1 but I don't want to re-count the times when BU = BU 1 AND OE = OE1
Any thoughts?
-
6. Re: Calculated Field to Pull Information from Two Different Fields
Josh Delekta Oct 20, 2016 5:47 AM (in response to John Croft)I put together an example workbook and attached here.
The total in the data is $2,785,000
If you filter on BU1 the total is $550,000
If you filter on OE1 the total is $585,000
If you filter on BU1 and OE1 the total is $200,000
The total should be everything that hits BU1 and OE1. However, I don’t want to double count the amounts that hit both BU1 and OE1.
The total I am expecting from the attached example is $935,000.
It would include Supplier 1, 2, 3, 4, 5, 8 and 10. It would not double count Supplier 4 and 5 because they happen to include both BU1 and OE1.
Thoughts? Thank you. I appreciate it.
-
Sample.twbx 16.5 KB
-
-
7. Re: Calculated Field to Pull Information from Two Different Fields
John Croft Oct 20, 2016 7:37 AM (in response to Josh Delekta)I'm not sure if this is how you envision it but see attached. I'll let you look it over and replicate so you'll understand what my calculations are doing.
Essentially you need a calc for:
IF ATTR([Business Unit] = 'BU1' OR [Cost Center] = 'OE1') THEN SUM([Spend]) else 0 end
And then you can filter out anyone with a '0' spend in the calc above. And then you can look at running totals with another table calc.
Hope this maybe helps.
-
Sample.twbx 24.1 KB
-
-
8. Re: Calculated Field to Pull Information from Two Different Fields
John Croft Oct 20, 2016 7:58 AM (in response to John Croft)This is cleaner and maybe more what you're looking for.
-
Sample-Alternate.twbx 24.0 KB
-
-
9. Re: Calculated Field to Pull Information from Two Different Fields
Josh Delekta Oct 20, 2016 8:30 AM (in response to John Croft)Thanks John. I appreciate all your help. I will mark this as the correct answer because it works but I am now realizing unfortunately that my issue might be with the fact that I'm trying to apply the calculation you gave me to a secondary data source.
I can't share the data I am working on but my primary data source has "Contract" information and if the "Supplier" in that data source has an active contract it pulls the spend for them from a secondary data source where I'm trying to apply the calculation.
I'm not sure how to workaround that. Please let me know if you have any suggestions. Thank you again.
-
10. Re: Calculated Field to Pull Information from Two Different Fields
John Croft Oct 20, 2016 8:47 AM (in response to Josh Delekta)If you're using version 10, you may be able to join the data sources prior to pulling workbook sheet view.
Otherwise i you can send me a mock file that is blendable with the previous file you sent, I can look at modifying my example for you.
-
11. Re: Calculated Field to Pull Information from Two Different Fields
John Croft Oct 20, 2016 8:51 AM (in response to John Croft)Or Maybe you could look at flipping the secondary source to be primary.
-
12. Re: Calculated Field to Pull Information from Two Different Fields
John Croft Oct 20, 2016 8:58 AM (in response to John Croft)I added a sheet 2 in case it helps. If not, I may need to see a mock example file from you.
-
Sample-Alternate.twbx 48.0 KB
-
-
13. Re: Calculated Field to Pull Information from Two Different Fields
Josh Delekta Oct 20, 2016 9:32 AM (in response to John Croft)John,
Please see attached. I took your workbook and created the exact scenario I have currently on "Sheet 3". The "Supplier" field pulls from the "Contracts" data source and the "Spend" field pulls from the "Data" data source. We haven't moved to Tableau 10 yet unfortunately! I'm trying to flip the data source right now but I don't believe there is a way other than to re-create the dashboards all over again.
Please let me know what you think. Thank you!
-
Sample-Alternate 2.twbx 57.9 KB
-
-
14. Re: Calculated Field to Pull Information from Two Different Fields
John Croft Oct 20, 2016 10:07 AM (in response to Josh Delekta)I'll look at it. But yes you have to recreate to flip the data sources currently.