3 Replies Latest reply on Jul 26, 2018 3:25 PM by swaroop.gantela

    Add unique value from a datasource to a value from another datasource

    Rico de Feijter

      Hello,

       

      My question below pertains to the attached workbook.

       

      I have two datasources in use:

      In sheet 1, I have listed all 'Rekeningen'. One of the 'Rekeningen' is chosen as an example for this question, namely 'Rekening 0010', which comes from the Query datasource. As you see it has a CURR_BALANCE of 377.888,26:

       

      Then we have sheet 2. The structure of this sheet is the same. Here 'Rekening 0191' is taken as an example for this question and it comes from the 2018 - 3+ (Holding) datasource. It has a CURR_BALANCE of 2:

      Note that 0191 does not appear in the first data source.

       

      In sheet 3, I want to sum these values by using the CustomCalc calculation, which is as follows:

       

      This calculation results in:

       

      As you can see the value '2', which belongs to Rekening 0191 is not added to the value '377.888,26' and thus only the CURR_BALANCE beloning to 0010 is shown.

       

      However, it should still be possible to sum those values right? How can I do this?

        • 1. Re: Add unique value from a datasource to a value from another datasource
          swaroop.gantela

          Rico,

           

          I made an attempt at switching the primary source to be 2018Holding

          and then used a calculated field on the Query datasource of:

          WINDOW_SUM(

          IF ATTR([Rekening])='0010' THEN SUM([CURR_BALANCE])

          ELSEIF ATTR([2018 - 3+ (Holding)].[Rekening])='0191'

             THEN SUM([2018 - 3+ (Holding)].[CURR_BALANCE])

          END)

           

          In sheet calcTable it shows the levels of calculation,

          and sheet endResult shows the final value after some text filtering.

           

          Please see the workbook v10.5 attached  in the Forum Thread.

          • 2. Re: Add unique value from a datasource to a value from another datasource
            Rico de Feijter

            Hello Swaroop,

             

            First of all, thanks for your support!

             

            I have still one question.  Eventually I want to make a parameter to set the date for which the CURR_BALANCES must be shown. So in my workbook you find the MaxDateParameter. If you move 'Jaar' and 'Periode' to the filter shelf in sheet 1 and set it to year=2018 and periode=5, you see that a value of 11.582,39 corresponds to Rekening 0010.

             

             

            When we do the same in Sheet 2,

            We see that a value of 2 from Rekening 0191 falls within period=3, year=2018.

             

            The MaxDateParameter must look if there are values that have an equal or below period/year as the given MaxDateParameter period and year. So when we set the MaxDateParameter to

            in endResult, CustomCalc should add 11.582,39 (0010, in period 5, 2018) to 2 (0191, in period 3, 2018), since period 5, 2018 and period 3, 2018 are both equal or below the period/year in MaxDateParameter. Suppose that we changed MaxDateParameter from 05/2018 to 04/2018 then CustomCalcSum should show 7.201,89, since 7.199,89 is the first value below 04/2018 that exists in period 10, 2017 (see sheet1 below):

             

             

            However the 2 should still be added as this value is from period 3, 2018 as shown above (this explains 7.201,89 (7.199,89+2)) . If no value could be found a 0 should be returned. So when, we change the MaxDateParameter to 02/2018, a 0 should be added to 7.199,89, since there is no value earlier than 03/2018 in sheet 2.

             

            How can I achieve this?

             

             

            UPDATE:

             

            I attached the workbook in this post (see sheet endResult(2)  in the attached workbook). The parameter is working now for 0010 but not for 0191 (note that the addition has not yet been taken into account. e.g. 7.199,89 + 2 = 7.201,89):

             

             

             

            UPDATE:

             

            Fixed it , please check the attached workbook.

            • 3. Re: Add unique value from a datasource to a value from another datasource
              swaroop.gantela

              Rico,

               

              I'll have to admit that I'm not sure if I'm following all that has transpired.

              But in the end, if it is working for you, I'm glad if I was able to nudge along the way.

               

              I'm also not clear on how your datasources interact, and am wondering

              if there is not a more direct way to join 0010 and 0191.

              Possibly some setup in Tableau Prep could assist.