Multiple Fact Multi Grain Modelling

[#1]

Hello All,

I have a strange requirement for which I am working on the data model. Here is the brief of requirement:

I have 2 facts both are at different granularity.

Sales Fact--Has Mnth,Product,Store

Budget Fact-Has only Mnth and Store

When a straight table is created with Store,Product and Date my user would like to see same budget across all the products but shouldn't aggregate at higher level as it leads to sum up of same value multiple times:

StoreName	Year	PG	Product	SUM(SaleQty)	SUM(BudgetQty)
				19	11
Store A	2013	PG1	Product A	6	11
Store A	2013	PG1	Product B	7	11
Store A	2013	PG2	Product C	6	5

In the above example my user expects to see budget qty 11 instead of 5 because budget qty for Store A in 2013 is 11, but the problem here is Product C wasn't sold in one of the month.

I have attached an example for better understanding.

Thanks in advance.

Mc

[hic · Accepted solution]

This can be done using generic keys. See more on

http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/26/mixed-granularity

and

Generic keys

HIC

[#3]

Thank you once again Henric Cronström