6 Replies Latest reply on Jan 18, 2016 10:27 PM by Wim De Ridder

# Using column value in calculated field as smaller or equal condition to sum

Hi,

I am building a management dashboard which is basically a table of calculations for the months in a given year. I have trouble showing a certain type of running totals. I will explain by example...

The data set is:

CreationYM     LoanAmount     LoanClosedYM

201501            10,000              201503

201502            15,000              201504

201503            10,000              NULL

201504            12,000              NULL

What I would need to show is the following:

201501     201502     201503    201504

Loans               10,000      15,000       10,000     12,000

Open Loans     10,000      25,000       25,000     22,000

The Loans is the sum of LoanAmount in the CreationYM.

The Open Loans are the sum of the loans amount where their CreationYM is smaller or equal to the CreationYM column and having their closed date later than the CreationYM column or NULL.

I am looking for a calculated field to show the Open Loans by CreationYM.

Has anyone a solution available? Thanks in advance for the help. I searched the Internet but did not found a solution...

• ###### 1. Re: Using column value in calculated field as smaller or equal condition to sum

[In the future it would be best to take that table of your data and put it into a packaged workbook. Make sure you mention what version of Tableau you are using.]

Wim, what you're going to want to do is change that CreationYM (and LoanClosedYM) into real dates. To do this first change your CreationYM field to a string type field in the dimension window. Then create this formula in a calculated field:

MAKEDATE(INT( LEFT([Creation YM], 4) ), INT( RIGHT([Creation YM], 2) ), 1)

Put this on the column shelf and the loan amount on the text shelf.

I don't see the logic of open loan in your sample data (where are you getting the March & April values) so you'll have to give us more information. Posting a workbook with a bit more data would be best.

Cheers,

--Shawn

EDIT: OK now I see the logic. Post a packaged workbook and I'll show you how to do this.

• ###### 2. Re: Using column value in calculated field as smaller or equal condition to sum

Thanks for the advice and response Shawn. It is the logic on the open loans that is my actual question. I want to show the total of open loans by calendar month. This is driven by the closed date. So I am looking to add a second row to your solution above showing in the given month all the open loans. This is retrieved looking at the Closed Date; if the closed date is null or more recent than the date you show in the column of the report, the loan amount is taken into account as open. Do you have a solution for this?

• ###### 3. Re: Using column value in calculated field as smaller or equal condition to sum

Got a packaged workbook with some sample data?

--Shawn

• ###### 4. Re: Using column value in calculated field as smaller or equal condition to sum

Here is a package workbook with the data inside. Thanks in advance for your input...

• ###### 5. Re: Using column value in calculated field as smaller or equal condition to sum

In your real data do you have some sort of RecordID field?

--Shawn

• ###### 6. Re: Using column value in calculated field as smaller or equal condition to sum

Yes there is a unique ID. I have embedded a LoanID in the attached file.