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
Table A
| A.Date | A.Dim1 | A.Dim2 | A.Value |
|---|
| 1/1/2014 | A | A | 20 |
| 1/1/2014 | A | B | 10 |
| 2/1/2014 | A | A | 50 |
| 1/1/2014 | A | B | 10 |
| 2/1/2014 | A | B | 30 |
Table B
| B.Date | B.Dim1 | B.Dim2 | B.Value |
|---|
| 1/1/2014 | A | A | 200 |
| 2/2/2014 | A | A | 600 |
| 1/1/2014 | A | B | 100 |
| 1/1/2014 | A | D | 400 |
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:
| Dim1 | Dim2 | Sum(A.Value) | Sum(B.Value) |
|---|
| A | A | 20 | 200 |
| A | B | 20 | 100 |
If no dates were filtered, the output would be:
| Dim1 | Dim2 | Sum(A.Value) | Sum(B.Value) |
|---|
| A | A | 70 | 800 |
| A | B | 50 | 100 |
