Inventory and revenue calculation in a single document

[#1]

Hi

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.

[udit_kumar_sana]

Hi,

Pls find the attached qvw for the solution.

Thanks & Regards,

Udit

[#3]

Hi Udit,

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

[udit_kumar_sana]

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

[#5]

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...

[kalyandg]

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

[udit_kumar_sana]

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.

[udit_kumar_sana]

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.

[#9]

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.