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

    Calculations 2 datasources - Join on date

    Catherine Blais

      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:

       

      BanqueCash FlowDate
      A52016-02-16
      B102016-02-16
      C152016-02-16
      A52016-02-16
      B52016-02-16
      C52016-02-16
      A252016-02-16
      A302016-02-16
      B82016-02-17
      B10

      2016-02-19

       

       

       

      The second datasource shows the initial balance on different dates:

       

      BanqueInitial balanceDate
      A1002016-02-16
      B2002016-02-16
      C1002016-02-16
      A1652016-02-17
      B2152016-02-17
      C1202016-02-17
      A1652016-02-18
      B2232016-02-18
      C1202016-02-18
      A1652016-02-19
      B2232016-02-19
      C1202016-02-19

       

       

      This is what I get in Tableau:

       

      But is it possible to have this?

       

       

      Date
      Banque 2016-02-162016-02-172016-02-182016-02-19
      AInitial balance                
        100.00   
                        165.00                      165.00                    
        165.00   
      Cash Flow                  
        65.00   
                                 -                                 -                               
        -     
      Final balance                
        165.00   
                        165.00                      165.00                    
        165.00   
      BInitial 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   
      CInitial 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
          pooja.gandhi

          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
            Catherine Blais

            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

             

            Thanks for the quick answer,

             

            Catherine