Sum on two different tables in one visualization

[DorryD]

Summarizing on two different tables

Hi all,

i want to solve the following problem without changing my data modell:

Imagine you have the three following tables.

A:
Load * Inline
[
Nation , Client
A , One
B , Two
];

B:
Load * Inline
[
Client , Date , Revenue
One , 1 , 10
One , 2 , 20
One , 3 , 30
Two , 1 , 11
Two , 2 , 11
];

C:
Load * Inline
[
Nation , Date2 , Revenue2
A , 1 , 20
A , 2 , 20
A , 3 , 20
B , 1 , 20
B , 2 , 20
B , 3 , 20
B , 4 , 20
];

I want to make a bar chart with dimension = date and measure should be a combination of table B and C. For example, the bar on date = 1 should be like 21 (from table B) + 40 (from table C).

If i select for example Nation = 'A', then of course the bar should be for Date = 1 

10 (from table B) + 20 (from table C) = 30.

[DorryD]

Hi all,

i want to solve the following problem without changing my data modell:

Imagine you have the three following tables.

A:
Load * Inline
[
Nation , Client
A , One
B , Two
];

B:
Load * Inline
[
Client , Date , Revenue
One , 1 , 10
One , 2 , 20
One , 3 , 30
Two , 1 , 11
Two , 2 , 11
];

C:
Load * Inline
[
Nation , Date2 , Revenue2
A , 1 , 20
A , 2 , 20
A , 3 , 20
B , 1 , 20
B , 2 , 20
B , 3 , 20
B , 4 , 20
];

I want to make a bar chart with dimension = date and measure should be a combination of table B and C. For example, the bar on date = 1 should be like 21 (from table B) + 40 (from table C).

If i select for example Nation = 'A', then of course the bar should be for Date = 1 

10 (from table B) + 20 (from table C) = 30.

[edwin]

perhaps your script is simple just to get someone to give you a solution.  one thing i noticed is that you have some sort of hierarchy and the revenues is a roll up.  however,  the sum for nations dont match sum of clients - but that could be due to some internal business rules. 

having said that, focusing on the dates: this really should be solved by changing the data model.  you should have a calendar and the calendar is associated to the dates. 

however, for the spepcific problem you will have to create a new field on the fly that can be associated to both date fields.  i suspect you will need to expand on this but there should be a way to expand it using valueloop, but for illustration purposes use valuelist for now.  the pic on the left is my proposed solution the pics on the right shows the expected values.