topic Re: Inventory and revenue calculation in a single document in QlikView

Inventory and revenue calculation in a single document

Tue, 26 Nov 2013 10:38:20 GMT

Let’s say we have the following tables in the document:

FactInventory:

load * Inline [

DateKey, InventoryMovement, ProductKey

20131101, 3, 1

20131101, 2, 2

20131102, -1, 1

];

FactSales:

load * Inline [

DateKey, Revenue, ProductKey

20131101, 20 , 1

20131101, 35, 2

20131102, 15, 1

];

DimAsOfDate:

load * Inline [

DateKey, AsOfDateKey

20131101, 20131101

20131101, 20131102

20131102, 20131102

];

DimProduct:

load * Inline [

ProductKey, ProductName

1, Product 1

2, Product 2

];

The problem is that we have to create single pivot showing something like

but as you see the revenue is not correct

Or another version

and now inventory is showing only movements

The data structure is:

We were trying to unite two fact tables into one or change the structure in the orther way but still have no result.

Any idea is welcome.

Re: Inventory and revenue calculation in a single document

Anonymous — Tue, 26 Nov 2013 10:41:12 GMT

Andrijan

I would suggest concatenating your 2 Fact Tables.

And maybe you could also consider using applymap to your Dimension tables so you end up with just one table in you data model.

Best Regards, Bill

Re: Inventory and revenue calculation in a single document

Tue, 26 Nov 2013 11:35:51 GMT

Hi Bill,

Thank you for your reply.

But if I try to concatenate tables I get the structure like this

and I don't understand how to use applymap() function here as far as we still need DimAsOfDate table to calculate inventory properly.

Re: Inventory and revenue calculation in a single document

Anonymous — Tue, 26 Nov 2013 11:38:15 GMT

Andrijan

Don't worry about the ApplyMap() for now.

Does it work ok for you with your new Data Model ?

Best Regards, Bill

Re: Inventory and revenue calculation in a single document

Tue, 26 Nov 2013 11:41:11 GMT

No, unfortunatelly it doesn't.

We still have this. Both versions are not correct:

Re: Inventory and revenue calculation in a single document

Anonymous — Tue, 26 Nov 2013 11:53:11 GMT

Andrijan

Would you be able to share you qvw ?

Best Regards, Bill

Re: Re: Inventory and revenue calculation in a single document

Tue, 26 Nov 2013 11:56:48 GMT

Yes, sure

Re: Re: Re: Inventory and revenue calculation in a single document

Anonymous — Tue, 26 Nov 2013 19:48:18 GMT

Andrijan

Your dimension DimAsOfDate table has two values the same for DateKey as in 20131101.

DimAsOfDate:

load * Inline [

DateKey, AsOfDateKey

20131101, 20131101

20131101, 20131102

20131102, 20131102

];

So for your FactInventory table that also has two values the same for DateKey as in 20131101, is there an algorithm as to to which one relates to which DimAsOfDate row ?

Best Regards, Bill

Re: Inventory and revenue calculation in a single document

greg-anderson — Tue, 26 Nov 2013 19:57:13 GMT

The numbers are correct.

Your association of DateKey '20131101' with AsOfDateKey '20131102' is going to cause everything from both dates to show up under 20131102 in the first pivot table. Thus 35 shows up for Product 2 on both days (because it's from 20131101) and 35 (20+15) shows up for Product 1 on day 2 because it adds them.

When you use the first DateKey, the pivot table is properly displaying the values for the dates and products.

Re: Re: Inventory and revenue calculation in a single document

Wed, 27 Nov 2013 08:23:49 GMT

Hi Greg,

The result in the two pivot table is not the one I'm tring to get.

It should look like this:

showing the revenue at that date (usual sum) and inventory by that date (accumulative sum from the beginning).

The association of DateKey and AsOfDateKey is done to achieve accumulative sum by the date for inventory. It can show correct numbers if we separate inventory and revenue to two different pivot tables and use DateKey for Revenue and AsOfDateKey for Inventory. But the goal is to make it in a single table where we can use just one date.

Re: Re: Re: Inventory and revenue calculation in a single document

udit_kumar_sana — Wed, 27 Nov 2013 09:04:07 GMT

Hi,

Pls find the attached qvw for the solution.

Thanks & Regards,

Udit

Re: Re: Inventory and revenue calculation in a single document

Wed, 27 Nov 2013 09:32:12 GMT

Hi Udit,

Thatk you for the file. The calculation looks correct but it doesn't work properly when we try to filter by date

Re: Re: Re: Inventory and revenue calculation in a single document

udit_kumar_sana — Wed, 27 Nov 2013 09:47:19 GMT

Hi,

you have to make selection of both the dates i.e asofDateKey & Date Key as there are some records of as of Date key of date 20131102 exixts for both 20131102 & 20131101 of Date Key.

If required u have to make single date from this two date.

Thanks & Regards ,

Udit

Re: Re: Inventory and revenue calculation in a single document

Wed, 27 Nov 2013 09:59:06 GMT

Unfortunatelly, the second column in the pivot (related to inventory inventory) is not correct and the Product 2 disappeared.

This part of data should be selected:

And I shill don't know the way how to make it work with single date.

And we should provide simple date filtering to our users so it's not the way...

Re: Inventory and revenue calculation in a single document

kalyandg — Wed, 27 Nov 2013 10:21:20 GMT

hi,

first do the mapping load,

MappingRevenue:

Mapping Load DateKey,

Revenue

From FactSales;

then

in the Fact Inventory Table, put apply map as below

applymap('MappingRevenue', DateKey, 'N/A') as Revenue

then, u will have revenue in Inventory table.

Thanks,

Kalyan.D

Re: Inventory and revenue calculation in a single document

udit_kumar_sana — Wed, 27 Nov 2013 10:22:16 GMT

Is not possible to get as its required from the data u are having ,as for some records of as of Date key of date like 20131102 there exits for both 20131102 & 20131101 of Date Key.

Re: Inventory and revenue calculation in a single document

udit_kumar_sana — Wed, 27 Nov 2013 10:23:57 GMT

Its not possible as per data u have since,there are some records of as of Date key of date 20131102 exixts for both 20131102 & 20131101 of Date Key.

Re: Re: Inventory and revenue calculation in a single document

Wed, 27 Nov 2013 10:34:10 GMT

Ok, lets forget about AsOfDateKey that was just the way to calculate Inventory.

So, the initial data that we have ase like:

FactInventory:

load * Inline [

DateKey, InventoryMovement, ProductKey

20131101, 3, 1

20131101, 2, 2

20131102, -1, 1

];

FactSales:

load * Inline [

DateKey, Revenue, ProductKey

20131101, 20 , 1

20131101, 35, 2

20131102, 15, 1

];

DimProduct:

load * Inline [

ProductKey, ProductName

1, Product 1

2, Product 2

];

Any changes like concatenation of tables or creation service tables lile "AsOfDate" are possible. The goal is to get this meaning that real data has 70,000 products and 5 years of revenue and inventory data and more dimensions can be added in the future.