topic Re: Target values (Sales) need to be separated in QlikView

Target values (Sales) need to be separated

Thu, 23 Jan 2014 10:59:58 GMT

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:

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.

Re: Target values (Sales) need to be separated

hic — Thu, 23 Jan 2014 11:07:05 GMT

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

Re: Target values (Sales) need to be separated

Thu, 23 Jan 2014 11:47:03 GMT

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.

Re: Target values (Sales) need to be separated

hic — Thu, 23 Jan 2014 12:10:14 GMT

So, I would

Join Opportunity and Opportunity Line into one single fact table
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

Re: Target values (Sales) need to be separated

Thu, 23 Jan 2014 14:26:41 GMT

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

Re: Target values (Sales) need to be separated

hic — Thu, 23 Jan 2014 16:04:11 GMT

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

Re: Target values (Sales) need to be separated

Mon, 27 Jan 2014 11:33:49 GMT

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?

Re: Re: Target values (Sales) need to be separated

Tue, 28 Jan 2014 14:34:50 GMT

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.

Re: Re: Target values (Sales) need to be separated

hic — Tue, 28 Jan 2014 19:56:40 GMT

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

Re: Target values (Sales) need to be separated

Wed, 29 Jan 2014 11:01:37 GMT

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?

Re: Target values (Sales) need to be separated

hic — Wed, 29 Jan 2014 13:56:46 GMT

If you concatenate the two tables, "Targetmonth" and "OLI.Month" become the same thing, so you need to load these fields (and perhaps other fields also) by renaming them, e.g.

Targetmonth as Month

OLI.Month as Month

When this is done, you can click on a month and get both targets and opportunities for that month.

HIC

Re: Target values (Sales) need to be separated

Mon, 03 Feb 2014 13:36:20 GMT

Hi Henric,

the basic implementation is working now, many thanks.

But I have a strange behavior in my target values. They are twice as high as they should be. If i select a user and single month, it should be e.g. 101798,25, but the expression "sum(Targets)" returns twice as much.

In my table box, there are three lines for the month 201401, where I was expecting only two (for two different markets).

Any idea? Thanks again :-).

Re: Target values (Sales) need to be separated

hic — Mon, 03 Feb 2014 13:43:59 GMT

One way or another, you seem to have loaded the target values twice. Without the script or the app, it is impossible for me to say how that has happened.

HIC

Re: Target values (Sales) need to be separated

Tue, 04 Feb 2014 06:59:57 GMT

That was the deciding hint. I loaded the data twice when concatenating the targets table to the fact table. Solved now, many thanks again!

Regards

Jens