Getting budget data into sales model

[#1]

Hi,

I've come across an issue getting my companys budget data into my sales model.

I have attached a screenshot of the sales model for reference (QV_Model).

I've had no issues actually getting my budget data into qlikview via excel import, I'm just stumped as to what the best (or any) way is to associate it with my sales model.

The data in my sales model is all in counters (LEAD_COUNT,SOLD_COUNT etc) all at individual date level.

The data in my budget model is at week level. I have attached a screenshot of this too (QV_Budget).

I've been playing with having the budget info as a data island and just referring to it via set analsyis in my reports. Is there another way?

Thanks

Adam

[Gysbert_Wassenaar]

You have two fact tables with different granularity. See here for how use generic keys to deal with such cases.

[#3]

Thanks for your reply again Gysbert.

After reading the link it looks like I may have to amend all my fact tables.

At the minute I have 4 fact tables (Leads, Surveys, Sales, Installs [see attached above]) which each contain a primary key (JOB_ID), a datefield for that fact, and then the fact field(s).

To get my budget data into these fact tables (budget data is split by Depot, Group, Channel, WeekNo and KPI) will I need to add Depot, Group etc to them so that these generic keys work?

Slightly confused here and thinking my model might not allow for this solution?

Adam

[#4]

I've tweaked a few things which may help someone provide an answer to my problem here...

My 'Targets' table now contains data at Date level which matches my actual data.

I'm still confused about the most efficient way to tie this target data to my actual data though.

My 'Targets' table contains

• data for each KPI we are tracking (LEAD_COUNT, SET_COUNT, SAT_COUNT, INSTALL_COUNT, INSTALL_REV, SALE_COUNT, SALE_REV)
• the DEPOT for each of these KPI's (which needs to be associated with DEPOT in MainJob so I can filter on just this DEPOT field for both actual and target)
• the DATE for each KPI (which needs to be associated with the CommonCalendar and each KPI calendar) so that I can select a Week from CommonCalendar and it group both the actual and target data for that week.

If anyone can give me some guidance (I'm not sure generic keys will work in this instance) this would be much appreciated.

If its easier to attach a copy of my qvw then let me know.

Adam