1 2 Previous Next 18 Replies Latest reply on Oct 21, 2016 6:54 AM by Josh Delekta

Calculated Field to Pull Information from Two Different Fields

Josh Delekta Oct 19, 2016 10:49 AM

Hello,

I am looking for the best way to accomplish the following: I have four fields/columns. "Supplier", "Business Unit", "Operating Expense" and "Spend". I am currently showing the spend for each supplier in my worksheet. However, I'd like to filter results based on 1 value from the "Business Unit" field and one from the "Operating Expense" field. Is there and and/or statement I could use?

For example, my data source includes:

SUP1, SUP2, SUP3

BU1, BU2, BU3

OE 1, OE2, OE3

SUP1 has spend related to BU1.

SUP2 and SUP3 have spend related to BU2 and BU3.

SUP1 has spend in OE1 and OE2 and SUP2 and SUP3 have spend with OE3.

If I filter based on BU1 I remove the data for SUP2 and SUP3.

If I filter based on OE2 and OE3 I remove the data for BU1

Is there a statement for, include "Spend" data for "BU1" and "OE2" without removing results from one or the other and also not double counting the "Suppliers" that have spend in both "BU1" and "OE2"?

Thank you.

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.
Like Show 0 Likes(0)

Actions
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?
Like Show 0 Likes(0)

Actions
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.
Like Show 0 Likes(0)

Actions
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
Like Show 0 Likes(0)

Actions
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?
Like Show 0 Likes(0)

Actions
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
Like Show 0 Likes(0)

Actions
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
Like Show 0 Likes(0)

Actions
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

3 of 3 people found this helpful
Like Show 2 Likes(2)

Actions
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.
Like Show 0 Likes(0)

Actions
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.
Like Show 0 Likes(0)

Actions
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.
Like Show 0 Likes(0)

Actions
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
Like Show 0 Likes(0)

Actions
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
Like Show 0 Likes(0)

Actions
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.
Like Show 0 Likes(0)

Actions