2 Replies Latest reply on Feb 23, 2016 2:38 PM by Catherine Blais

Calculations 2 datasources - Join on date

Hi,

I have two different datasources that I need to join to make analysis (and it’s not possible to join them in the same datasource).

The first datasource gives the cash flows by banks on different dates.

Example:

 Banque Cash Flow Date A 5 2016-02-16 B 10 2016-02-16 C 15 2016-02-16 A 5 2016-02-16 B 5 2016-02-16 C 5 2016-02-16 A 25 2016-02-16 A 30 2016-02-16 B 8 2016-02-17 B 10 2016-02-19

The second datasource shows the initial balance on different dates:

 Banque Initial balance Date A 100 2016-02-16 B 200 2016-02-16 C 100 2016-02-16 A 165 2016-02-17 B 215 2016-02-17 C 120 2016-02-17 A 165 2016-02-18 B 223 2016-02-18 C 120 2016-02-18 A 165 2016-02-19 B 223 2016-02-19 C 120 2016-02-19

This is what I get in Tableau:

But is it possible to have this?

 Date Banque 2016-02-16 2016-02-17 2016-02-18 2016-02-19 A Initial balance 100.00 165.00 165.00 165.00 Cash Flow 65.00 - - - Final balance 165.00 165.00 165.00 165.00 B Initial balance 200.00 215.00 223.00 223.00 Cash Flow 15.00 8.00 - 10.00 Final balance 215.00 223.00 223.00 233.00 C Initial balance 100.00 120.00 120.00 120.00 Cash Flow 20.00 - - - Final balance 120.00 120.00 120.00 120.00

Tableau doesn't seem to be able to join correctly the 2 datasources by the bank AND the date.

As you can see, the final balance = initial balance + Cash flow

The initial balance is equal to the final balance of the day before.

Thanks a lot for your help,

Catherine

• 1. Re: Calculations 2 datasources - Join on date

Hi Catherine!

Which version of Tableau are you using? It is important to make sure that you activate the blend by ensuring the orange link is on for both banque and date not just date.

Here I kept Cash flow as the primary source (determined by the 1st field you drag on the view) and initial balance as the second source. Instead of using the initial balance field directly, you can create a calc for it to fill 0 where its null:

Initial Balance Calc: ZN(lookup(SUM([Initial balance (Test_Exports_Solde2)].[Initial balance]),0))

Final Balance Calc would just be: IFnull(lookup(sum([Cash Flow]) + SUM([Initial balance (Test_Exports_Solde2)].[Initial balance]),0), PREVIOUS_VALUE(lookup(sum([Cash Flow]) + SUM([Initial balance (Test_Exports_Solde2)].[Initial balance]),0)))

The lookup function basically saying, if the value is null fill with previous values.

Bringing all of these fields described above, you do end up getting the view you are seeking:

Hope this helps!

Pooja.

• 2. Re: Calculations 2 datasources - Join on date

Hi Pooja,

Thanks a lot, it works! I also tried to add a new column for different currencies and it worked.

In the datasource for the initial balance, it is not sure we will have the future balance for the next days. So I tried a new Tableau with a datasource that only shows the initial balance for the first date and then I make the calculation below to take the final balance of the day before, and it worked:

IF ISNULL(SUM([Initial balance (Initial balance)].[Initial balance]))

THEN LOOKUP([Final Balance Calculation],-1)

ELSE SUM([Initial balance (Initial balance)].[Initial balance])

END