Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Inventory and revenue calculation in a single document

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.

       

17 Replies
Anonymous
Not applicable
Author

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

Not applicable
Author

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.

Anonymous
Not applicable
Author

Andrijan

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

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

Best Regards,     Bill

Not applicable
Author

No, unfortunatelly it doesn't.

We still have this. Both versions are not correct:

Anonymous
Not applicable
Author

Andrijan

Would you be able to share you qvw ?

Best Regards,     Bill

Not applicable
Author

Yes, sure

Anonymous
Not applicable
Author

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

greg-anderson
Luminary Alumni
Luminary Alumni

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.


Not applicable
Author

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.