I have a fact table that contains sales information that goes at the product level and this table is linked with a Category table which has product Category -> Group -> Sub Group.
The fact table links with the Category table with the Sub Group code (each product has a sub group).
The problem is i have a sales target table that has the sales target for the Category, it does not have the sales target for Group and SubGroup, so i cannot SUM by subgroup to display the data.
For example, if i would show a table that contains the following columns:
What can I do to show the Sales Target? Since the fact table is always filtered by SubGroup, i cannot just CONCATENATE and SUM it because the sales target does not have the subgroup information. I would have to query the Sales Target on the fly, i guess. I just do not know how.
I cannot do this in a separate table, because the sales target is also divided by Store and Date and those two tables links with the Fact. If i separate it in two Facts it would do a ciclic relation.
Hmmm, no, because that already has a circular reference problem. So I guess I'm not sure what your data currently looks like.
Can you give some example data, and what sort of results you're looking for for that example data? Ideally, post a sample QlikView file with that data?
After some head scratching i got the obvious solution.
I had only one category table with category, group and sub-group. So i separated it on three tables, one for category, one for group and one for subgroup. This way it was easy to link the sales target by category table to the category table.
