data model issue

[#1]

Dear all,

I  structured my model as follows (see below)

Now, when I try to do a calculation per month on the Fact_Headcount I get either double (triple , etc) counts because the link with the mapping table is on "period-level" and that the lowest granularity for the mapping table is period-name; with multiple people having a same level.

How I can make Qlik do the calculation only once for every unique combination of period and level without the double counts ?

Thank you in advance,

Regards,

Kevin

[marcus_sommer]

I think there isn't any possibilty to solve this with calculations. You will need to change your data-source(s) (which is probably difficult to impossible) or the datamodel.

For this I would concatenate the both fact-tables and linking all dimensions directly without a link-table approach. With this methods the matching from different granularities is quite easy. I use this often, for example by sales-data on day-level and budget-data on month-level by which I with makedate(year, month, 1) a date create.

- Marcus

[#3]

Thank you Marcus for your quick response.

For dates I agree that making a date out of the month would work. How would this be possible in the context of (slowly changing) product and product group for example? (actuals and dimension per product, budget per product group)

Regards,

Kevin

[marcus_sommer]

I have a similar case with a KPI over all sales which is incompleted parted in different sales-main/sub-categories. The missing parts and their distribution over all channels/stores will be done per percentage calculation. This is quite difficult and needs a lot of efforts and will be performed in many steps - alone to extract the relevant data from our monster planning-xls is horrible.

But in short: if it's not enough to compare grouped budget-data with then grouped actuals you will need to distribute to the smallest wanted granularity.

- Marcus