Problem with Hierarchy

[#1]

Hi,

I have imported a group of table that contain the following informations:

- sales table containing customer numbern item number and turnover

- customer table containing customer number, name of the customer, and hierarchy (differents customers belong to one hierarchy)

- items table containing item number and product name

- items list containing the hierarchy number and the item numbers (those items must obligatory be sold in this hierarchy)

Using those datas, it is very easy to obtain the sales by customers, by item, by hierarchy... But what I want to show is the sales by hierarchy with products grouped by 2 categories. the first category is the items that must be sold in the hierarchy, and the second one the other items sold but not listed for the selected hierarchy. See my example in qvw, and the excel spreadsheet for datas.

regards

[hic]

From a data model perspective, the Customer hierarchy and the Product hierarchy are two different things. So you must load them in different fields. Then you can make the table you want. See attachment.

[#3]

Dear Henric,

thanks for your answer. Unfortunately, your solution is different of what I need. The Hierarchy concerns only customers.

In fact, we negociate with holding a list of product. The products must after negociation be sold in every shops of the hierarchy. That's what we want to analyze.

With your proposal, we are able to see customer hierarchy H1 and product hierarchy H2 alltogether. What I only want to see are the products listed in each hierarchy, and not those which are not listed.

[hic]

What if you include this information in the fact table? And only load the records where the item sold is in fact listed? See attachment.