Chart by month showing sums from different tables

[#1]

Hello,

I have two complementary tables, one for sales orders to customers and one for buying orders from suppliers.

I wish to compare month by month the sum of price orders of both the entities. What do I have to use as dimension in this case? If I create a new calculated field as year+month from sales date field and use it as dimension then sum sales are ok, but not buying. And viceversa.

Thanks for any reply.

Regards.

--

Marco Pikiri

[#2]

Sorry but can't understand how to continue the data model I've pasted before.

I've tried to create an "OrdersLink" table starting again from "Project" table, that concatenates horizontally both the Sales and Purchases keys:

Please note how original tables are changed in front of the ones you saw before. Sales and Purchases keys are composed by company, year, order number and row number fields, project key is composed by company and project id.

Anyway how do I have to continue my model on the left side? ("OrdersLink" table). I supposed to load again the two tables and linking them to OrdersLink table, but probably I didn't understood your suggestion because I'm afraid I'm missing something.

Thanks, regards.

--

Marco Pikiri

[timanshu]

Hi, you can create a separate Master calendar having both dates ie sales and purchase dates included.take minimum date considering both tables and same for maximum date and create a master table link both dates of purchase and sales to newly created master table.

[#4]

Hi,

ok about the creation of master calendar, but please see data model I pasted.

Starting also from HIC documentation linked by swuehl I don't know how to link calendar entity, because as you can see I have a central "Project" table from which various roads start, going separately to Sales tables and Purchases tables.

In this condition how do I implement master calendar?

Thanks in advance.

--

Marco Pikiri

[timanshu]

Hi,

Make a new table by concatenating sales & purchase tables with a flag field added that will differentiate b/w the two. then u can create master calendar from concatenated date field. This will solve the purpose. Attach QVW or data files. I will do it for u.

[ankitbisht01]

Hello , I have made sample data in inline tables and as same filed name as yours, and solved your data modeling issue and some charts also there

PFA

Thanks