Conformed dimensions (preventing loops)

[#1]

Hello everybody,

I have a question about an issue I would know how to solve with other BI tools, but I'm stuck on with QlikView. Simplified example:

A project creates revenue on a certain date, and is associated with a certain region. We report on project revenue on the dimensions time (month/year) and region with drilldown to project (lowest grain). On the other hand we have a target defined per region per month (not per project). See attached example: normally I would create to fact fables (one with project/ revenue and one with target) with conformed dimensions time and region. When doing so in QlikView I create a loop and one relation will be disconnected.

I've been trying to solve this problem by creating an alias or a calender. The alias doesn't work for me because it doesn't allow me to use my dimensions properly (for example having to make 2 selections on region revenue and region target instead of one selection on region), with the calender my dates are more organized but my loop remains.

I feel like there should be a really easy solution to this (common) problem, hope you can help me.

[michael_anthony · Accepted solution]

Concatenate your Target table with you Sales table, although you'll need to convert the Target month into a Date value (eg. 1st of month).

Keep the "Sales" field for actuals $ and "Target" for target $, unless you have many equivalent value fields, in which case consider an identifier field.

You will run into issues is if there are other characteristics of Projects that user may select on, eg. manager, status etc. Because Target won't have any of those characteristics any selection will automatically exclude the target records. You can overcome this in the charts by using set analysis though.

[#3]

There are other characteristics of projects users may select on and want to see when they select the revenue of projects for a certain month/region, losing that detail level is not an option.

I'm triggered by your remark about set analysis, maybe it would work if I set the "target region" to be equal to a variable containing the selected "project region", that way I don't bother users with making double selections.