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

# Finding out account gain loss

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

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