1 Reply Latest reply on Oct 15, 2018 3:30 PM by Andrew Bickert

    Finding out account gain loss

    Vishal DSouza

      Hello Folks,

       

      I am trying to perform a calculation in tableau which can tell me how many new accounts came in and out for a given two dates.

       

      lets consider two months data, January and February.

      • If the account is displayed in January and doesnt show up in February then we consider it as a loss. We lost it.
      • If the account is not available in January and shows up in February then we consider it as a Gain. Basically new addition.

       

      I have attached excel spreadsheet. My aim is to make user select two dates and the result should be available in dashboard. Could any of you be kind enough to guide me through?

       

      Many Thanks,

      Vishal

        • 1. Re: Finding out account gain loss
          Andrew Bickert

          Hi Vishal,

           

          You can accomplish this by using a table calculation to check if there is a value in the start date as well as the end date. Here is an example formula with your data and the output:

           

          if((ZN(SUM([Dollar Value])) - LOOKUP(ZN(SUM([Dollar Value])), -1)) / ABS(LOOKUP(ZN(SUM([Dollar Value])), -1))=-1) then "Loss" else if(str((IF (attr([Date])=[End]) then

          isnull((ZN(SUM([Dollar Value])) - LOOKUP(ZN(SUM([Dollar Value])), -1)) / ABS(LOOKUP(ZN(SUM([Dollar Value])), -1))) END))="1") then "Gain" end END

           

          Which would give you this:

           

          Attached is a workbook for reference.


          Andrew