2 Replies Latest reply: Mar 21, 2014 2:07 AM by Joseph Dalton RSS

    Straight table with data islands

    Joseph Dalton

      I'm trying to figure out how to create a straight table which will correctly sum up values from different data islands.

       

      We have the following tables (all 3 are data islands, the user will have a date selection on A.Date):

       

      Island List

      Dim1Dim2
      AA
      AB

       

      Table A

      A.DateA.Dim1A.Dim2A.Value
      1/1/2014AA20
      1/1/2014AB10
      2/1/2014AA50
      1/1/2014AB10
      2/1/2014AB30


      Table B

      B.DateB.Dim1B.Dim2B.Value
      1/1/2014AA200
      2/2/2014AA600
      1/1/2014AB100
      1/1/2014AD400


      The goal is to create a straight table that for each combination of dimensions in the Island List, in one column it will show the sum of A.Value where the A.Dim1=Dim1, A.Dim2=Dim2, and the other column will be the sum of B.Value where B.Dim1=Dim1, B.Dim2=Dim2.

       

      The user will have a date selection on A.Date, and we only want the values from Table B that match the dates that are selected in A.Date. This condition is something we may be able to get rid of (by linking A.Date and B.Date).

       

      Data model transformations are possible (if that helps to solve this). but additional linkages are not, other than what is specified above.

       

      ***EDIT***

       

      The desired output in this situation would be a table that looks like this, if 1/1/2014 was selected as the date:

      Dim1Dim2Sum(A.Value)Sum(B.Value)
      AA20200
      AB20100

       

      If no dates were filtered, the output would be:

      Dim1Dim2Sum(A.Value)Sum(B.Value)
      AA70800
      AB50100