3 Replies Latest reply on Aug 23, 2018 1:46 PM by Jim Dehner

    Calculation using a data source fields and calculated field ver 10.5.6

    Vanessa Leineweber

      I'm hoping I can get this resolved.  I have different locations and we need to create a tax field.  I can get the totals for everything else because I can sum the dollar amounts, but the tax is a calculated field 95% of the time, due to an exception report it shows up in the data source..

       

      Administration and Business are calculated with the -.14 and Company Drug is calculated at -.03

       

      I am attaching a workbook, we are version 10.5.6.

       

      Thank you, your help is truly appreciated.

       

      I ultimately want it to look like this.

       

       

      W Roll UpAdministrationBusinessCompany Drug
      COLLECTIONS-35947175810157610
      TAX5033-24613-4728

      TOTAL

      -30914151197152882
        • 1. Re: Calculation using a data source fields and calculated field ver 10.5.6
          Jim Dehner

          see the attached

           

          it is a "brute force way " to get this result

           

          the total formula is

           

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Calculation using a data source fields and calculated field ver 10.5.6
            Vanessa Leineweber

            Hi Jim,

            It works, however. I am attaching a screenshot of the actual report.  I'm not sure why the tax calculation is hitting the other numbers when I have the case looking at the "Collections" and "Administration".  It should not calculate for the others.  Also we group the totals for Charges, Net Collections (Collections, tax, and Clinical Services (if any)), Total Expenses (all others listed).  We then have what is called the "Contribution Margin" which is the Net collections - Total Expenses.

             

            So for the example I don't need the tax to be on any line except it's own.

             

            My apologies, but I'm new at this and not sure if Tableau is the way to go because we are creating P&L statements for the facilities and I know that Tableau is a visual and I would eventually like to add graphs to the P&Ls for those who prefer graphs.

             

            Thank you for all of your help.

            • 3. Re: Calculation using a data source fields and calculated field ver 10.5.6
              Jim Dehner

              Hi

               

              I would have to see at least some of the real data to see what is goin on but try this

              I  don't use the case statement the way you used it - try rewriting it to this - it made no difference on the small sample data I had - what you had should have worked

               

              if [W Roll Up] = "Collections" then

              case [P&L Text]

              WHEN  "Administration" THEN ([Actual] * -(.14))

              WHEN  "Business" THEN ([Actual] * -(.14))

              WHEN "Company Drug" THEN ([Actual] * -(.03))

              ELSE 0

              END

              else 0

              end

               

              Jim