1 2 Previous Next 18 Replies Latest reply on Oct 21, 2016 6:54 AM by Josh Delekta Go to original post
      • 15. Re: Calculated Field to Pull Information from Two Different Fields
        John Croft

        See attached. I think you just need to make sure you adjust your filter correctly. Unless I'm missing what your last post was telling / asking.

         

        • 16. Re: Calculated Field to Pull Information from Two Different Fields
          Josh Delekta

          It looks like I have the filter applied correctly based on your screen shot below but it keeps filtering on only "Suppliers" who have both "BU1" and "OE1". Right now I am not seeing results for "Suppliers" who have "BU1" but don't have "OE1" and I'm not seeing "Suppliers" who have "OE1" but don't have "BU1". Is my calculation below incorrect? It looks like an OR statement but it seems to be pulling it like its an AND statement.

           

          IF ATTR([Business_Unit] = 'Legal' OR [Cost_Center] = 'Legal - Operating Expense') THEN SUM([Translated Debit Minus Credit Amount]) else 0 end

           

          Thank you.

           

          Clarification: The formula above is my actual formula and isn't based on the sheet you've been sharing with me.

          • 17. Re: Calculated Field to Pull Information from Two Different Fields
            John Croft

            Ok. If Sheet three is what your situation looks like where Contracts is the primary data source and Data is the secondary data source, I'll post my calc below and speak to how / why it's different.

             

            IF (ATTR([Sheet1 (Data)].[Business Unit]) = 'BU1' OR ATTR([Sheet1 (Data)].[Cost Center]) = 'OE1')

                THEN SUM([Sheet1 (Data)].[Spend])

            else 0

            end

             

             

            1) when referencing fields across data sources, you need to reference the field as [datasource name].[field name] so where you have [Business_Unit], you really need [datasource name].[Business_Unit]

            2) You would then need to aggregate that bc Tabluea joins at aggregate levels. so it would become ATTR([datasource name].[Business_Unit])

            3) Same thing for [Cost_Center] ->  ATTR([datasource name].[Cost_Center])

            4) Same thing for SUM([Translated Debit Minus Credit Amount]) -> SUM([datasource name].[Translated Debit Minus Credit Amount])

            5)So then I have many more parens than you. If I modify your to resemble mine, I would get :

             

                IF (ATTR([datasource name].[Business_Unit]) = 'Legal' OR ATTR([datasource name].[Cost_Center]) = 'Legal - Operating Expense')

                      THEN SUM([datasource name].[Translated Debit Minus Credit Amount])

                else 0

                end

             

            6) If [Business_Unit] [Cost_Center] and [Translated Debit Minus Credit Amount] are n your primary then your calc should work.

             

            Attached is a revised workbook with sheets 3 and 4. One of them should give you what you need.

            • 18. Re: Calculated Field to Pull Information from Two Different Fields
              Josh Delekta

              John,

               

              Thank you again for all your help on this. I'm still getting the same result where its only returning results if its both "BU" and "OE". This is my code:

               

              IF (ATTR([operating_spend_by_supplier].[Business_Unit]) = 'Legal'

              OR ATTR([operating_spend_by_supplier].[Cost_Center]) = 'Legal - Operating Expense')

              THEN SUM([operating_spend_by_supplier].[Translated Debit Minus Credit Amount])

              else 0 end

               

              Two questions:

               

              1) Would "Translated Debit Minus Credit Amount" coming from a CSV file cause any issues?

              2) In the sample you sent me I can see that the data and contracts data sources are switched between primary on each sheet but the code is exactly the same. Is there a second version of the code?

               

              Thank you!

              1 2 Previous Next