Elimination of Internal Business Volume

[bhorneber]

Hi,

I would like to eliminate internal business volume, when aggregating volume within a hierarchy.

As an example, let's assume this data set:

Sales Region	Seller	Buyer	Volume
CA	Factory_CA	Factory_US	10
CA	Factory_CA	Customer	40
DE	Factory_DE	Factory_US	20
DE	Factory_DE	Customer	50
US	Factory_US	Customer	200

With this regional hierarchy:

Country	Continent	World
CA	North America	World
DE	Europe	World
US	North America	World

Summing the data up by sales region to calculate the sales would result in a total of 320 globally (QlikView standard).

But, as some of the sales happens internal and have to be ignored when showing continents or the world, the consolidated sales figures are slightly different. 290 in this case:   

Region	Sales	Sales (consolidated)
DE	70	70
Europe	70	70
CA	50	50
US	200	200
North America	250	240
World	320	290

I would appreciate any ideas on how to achieve this form of data consolidation best in QlikView.

As this is kind of a common thing to do in income statement, maybe there are already papers out there ... unfortunately I couldn't find any.

Thanks a lot!

[Nicole-Smith]

You should be able to do it using set analysis.  Please see the attached example.

sum({<Buyer={'Customer'}>}Volume)

[bhorneber]

That would work, if I am only interested in customer volume, but I am interested at the sales volume on each level.

From a CA perspective, that would be 50 (10 sales to the US, 40 to a customer), analog for DE.

Also for Europe, the sales volume would be 70.

Only on the World level the amount that DE sold to the US factory is being ignored.

[Nicole-Smith]

I'm not sure I fully understand the logic behind what you're trying to do.  Maybe someone else can chime in to help you...

[bhorneber]

Let me try to elaborate - maybe that will help others too.

From a business point of view, let's assume an organization owns several factories. Those factories produce stuff that is sold to customers (external business). They also buy material from other factories (internal business).

In the above example, the factory in CA sold something for $40 to a customer and for $10 to the US factory.

So from Canada's factory perspective, their sales is $50.

The region head of North America is now interested in knowing the sales of the whole region. Although CA had $50 in sales and the US had $200 in sales, the total sales in this scenario is not $250, but $240. Because $10 were internal business.

Look at it from this point: If CA didn't sell something for $10, but for $1,000 and that would be added to the overall sales, it would totally misrepresent the business in North America.