topic Query a target value on an expression in QlikView

Query a target value on an expression

Thu, 17 Mar 2011 14:49:04 GMT

Hi,

I'm in trouble! help me 🙂

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:

Product Category (Aggregation) | Sales | Sales Target

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.

Query a target value on an expression

Thu, 17 Mar 2011 14:56:02 GMT

Important:

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.

Query a target value on an expression

johnw — Thu, 17 Mar 2011 22:36:29 GMT

So you have tables like this?

salesfacts:
product, subgroup, sales, store, date

categories:
category, group, subgroup

salestargets:
category, target, store, date

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?

Query a target value on an expression

Fri, 18 Mar 2011 00:24:23 GMT

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.

So obvious... 🙂

Too much QlikView on one week does this.. hehe

Thank you!