Budget table with different granularity

[#1]

Hello,

I received two budgets table from two different departments of the same company.

The first one has the following fields:

• Sales rep
• Month
• Budget

The second one has the following fields:

• Sales rep
• Customer number
• Month
• Budget

I received the first table way before the second one, and I showed the comparison between actual numbers and budget numbers like this:

• Sales rep
• Month
• Actual
• Budget

Now, I need to show the comparison between actual numbers and budget numbers on the sales rep level for the two departments, but also on the customer level, and I don't know how I can handle the missing customer field on the first table.

Would generic keys be the solution? In this case, I have different granularity on one fact table.

[hic]

Yes! Generic keys would be the solution.

See more on Fact Table with Mixed Granularity

HIC

[Oleg_Troyansky]

Yes, generic key is your solution in this case. The budget from the first department should be populated with Customer = *ALL* and Customer *ALL* needs  to get associated with all customers in the Customers table via generic link table.

I'm describing this solution in great detail in my new book QlikView Your Business, that's coming out in a month or so...

cheers,

Oleg Troyansky

www.masterssummit.com - take your QlikView skills to the next level!

[#4]

Thank you for the quick answer. I've read the brief on generic keys that you provided a few times and it is still not very clear to me.

As Oleg said, I get that I should populate my table from the first department with Customer = *ALL*, but the generic key in the dimensional link table is still a bit confusing. As far as I understand, it should look like this:

• %CustomerID (my generic key which should be linked to the fact table)
• CustomerID (and this should be linked to my dimensional customer table)

But what should my %CustomerID key be composed of in my case? SalesRep&CustomerID, CustomerID&CustomerID, something else?

[buzzy996]

simply u can concatenate through concatenate key word in ur script and play from front end comparisons accordingly.

[Oleg_Troyansky · Accepted solution]

You should build your dimension link table in two steps:

1. Associate each "true" Customer ID to itself, only named differently - something like this:

Customer_Link:

LOAD

     CustomerID,

     CustomerID as %CustomerID

resident

     Customers

;

2. Then, associate the value *ALL* with all Customers:

LOAD

     CustomerID,

     '*ALL*' as %CustomerID

resident

     Customers

;

The two loads will get automatically concatenated into a single table.

The only tricky part in the UI will be comparing Budget to Actual, when the user selects specific Customers. One budget will get reduced to the appropriate values for the individual customers, and the other department's budget will still appear as a whole. You should add a warning about that.

cheers,

Oleg Troyansky

[#7]

A little late, but thank you for the answer. I finally managed to make it work.