Target values (Sales) need to be separated

[#1]

Hi,

I have a bar chart that displays the target values for a sales employee per month. The input data for both target values and actuals is divided in two product groups, 'A' and 'B'.

So what I would like to have is target values bar for both groups together. But if the user selects either 'A' or 'B', the target values shall be adapted to only reflect the target value for the corresponding group.

Example:

               A          B

Target     100         50

Actuals     80         45

This works correctly in my solution for months where I have actuals for both groups 'A' and 'B'. The problem are months where is no data for either 'A' or 'B' products. This leads to an automatic filtering for  the product group like here

Situation:

               A          B

Target     100       50

Actuals     -         45

Displayed in the bar: 

               B

Target     50

Actuals   45

That is somehow logical, but without filtering for a product himself, the sales guy working with this dashboard will hardly understand why this month's target is only 50 and not 150 (overall).

That's my expression:

sum({<TARGET.Month=P(Calendardate), TARGET.Produkt=P(Productname)>} TARGET.Value)

Do you have an idea how to make it clear to the user, how the currently displayed target value is calculated? The chart is a grouped chart, is it possible to stack only this single bar to a combined target value with two different color? Any other approach?

Thanks.

[hic]

This very much depends on which data model you use. Hence - if you change your data model, you will probably get the behaviour you want.

Further, what you have written in your Set Analysis expression, is to simulate the associative logic that QlikVIew can do out-of-the-box if you make your data model right. You most likely don't need any Set Analysis at all.

Post your data model, an let us have a look at it.

HIC

[#3]

Another issue, the same good supporter. Hi Henric :-).

Ok, here it is:

To  make things a bit easier, I called the 'Abschlusstermin' 'Calendardate' in the expression above, where I have to say that OLI.SERVICE_DATE is the date connection to the data and 'Abschlusstermin' has the format 'YYYYMM' that the user can select the desired month from.

[hic · Accepted solution]

So, I would

1. Join Opportunity and Opportunity Line into one single fact table
2. Concatenate the Target table onto the fact table

The advantage is that you can then use one common dimension for Territory and another common dimension for the calendar (Abschlusstermine). Further, the above problem that some target values get excluded by a selection in Month will not exist.

Read more about how to do this here: http://community.qlik.com/blogs/qlikviewdesignblog/2012/10/26/mixed-granularity

HIC

[#5]

I've read your related article, but can't figure out which fields to use for generic keys. Can you please help?

[hic]

All fields that are common to the opportunities and targets should be named the same.

Further, you only have one key that is a candidate for being a generic key: OpportunityDate/TargetMonth. The simplest approach is to just use "Month" as key instead. You can still have OpportunityDate as an additional field in the opportunities part of the fact table (NULL in the target part).

Then you link your master calendar to the Month field.

HIC

[#7]

I tried to implement it, but now my target table is gone and I don't have any OPP_TMP.OPPORTUNITY_ID in my data any more.

Can you please help again?

[#8]

Hi,

as I didn't know how to solve this, I've created a small model to understand the correct approach. Could you please check I understand the basis correctly?

Second thing: My link table within this document is created manually, how can I implement an "real" version? I don't reach the same result when coding it.

Thanks.

[hic]

It is a relevant link table, but you need to create composite keys in a real situation since you want to link the date also.

However, what I suggested is something else. What I usually do instead of a link table, is to create one common fact table for both budget and actuals, or in your case, Target and Opportunities. The basic script is the following:

[Common Fact Table]:

Load ... , 'Opportunity' as Type From [Opportunity Lines] ;

Join ([Common Fact Table]) Load ... From Opportunities;

Concatenate ([Common Fact Table]) Load ... , 'Targets' as Type From [Target] ;

HIC

[#10]

Ok, so I did what you suggested, but it is still not working. If I select one month from either Targetmonths, OLI.Months or my linked calendar fiels (linked to the fact table via targetmonths), the other part gets excluded.

What am I doing wrong?