topic Re: Create a dynamic Target Line in QlikView

Create a dynamic Target Line

lessassy — Thu, 07 Jun 2018 11:55:32 GMT

Hello everyone,

I have a bar chart cycle drill (week and month) which highlights some KPI From 2015 to the Current Year.

Each year I have a monthly and a weekly target for that KPI, which is for example:

2015: 200 monthly and 50 weekly

2016: 175 monthly and x weekly

2017: 175 monthly and so on

2018: 200 ....

SO i would like my target to change based on my dates week and month.

The thing is i tried to put in excel but it doesn't work because i have a master calendar.

DO you have any solution ? like a specific formula to apply ?

The goal is to let me or one of the key user just write down the kpi number in a variable.

So the calculation could be done automatically.

But if it's not possible how do I do for the values in the excel i posted.

Because Qlikview won't recognized a field with only MM (example: January --> KPI = 200).

Do you have a solution ?

Here are my sample files attached.

Thank you

Re: Create a dynamic Target Line

Anonymous — Fri, 08 Jun 2018 10:42:16 GMT

FPA.

Re: Create a dynamic Target Line

lessassy — Fri, 08 Jun 2018 10:47:27 GMT

Thank you.

But the target you define is only for a specifc year.

I would like to scroll the bar chart and see the target moves.

Example: The target in december december should be at 175 and when i scroll to 2017 it changed.

Is it possible ?

Re: Create a dynamic Target Line

lessassy — Fri, 08 Jun 2018 10:59:00 GMT

Would it be possible to add

A column with year 2015 2016 2017 2018 and for each of these dates the value.

In that way, the target's reference line would change automatically

Re: Create a dynamic Target Line

Anonymous — Fri, 08 Jun 2018 11:02:42 GMT

Do you have target values for each month and week ? if so it is possible

Re: Create a dynamic Target Line

avkeep01 — Fri, 08 Jun 2018 11:18:45 GMT

Hi Yann,

That is possible when you breakdown your target. You can easily make a little script calculating the target by days. I multiplied the target of 175 by twelve because it looks like a monthly target to me.

// NoOfDays

temp01_Target:

NOCONCATENATE LOAD

Year,

COUNT(Created) AS [NoOfDays]

RESIDENT MasterCalendar

GROUP BY Year;

// target figures

LEFT JOIN (temp01_Target) LOAD

Year,

Target*12 AS Target

INLINE [

Year, Target

2018, 175

2017, 170

];

Target:

NOCONCATENATE LOAD

Year,

Target/[NoOfDays] AS Target

RESIDENT temp01_Target;

DROP TABLE temp01_Target;

LEFT JOIN (Target) LOAD

Year,

Created

RESIDENT MasterCalendar;

DROP FIELD Year FROM Target;

Re: Create a dynamic Target Line

lessassy — Fri, 08 Jun 2018 11:23:00 GMT

Yeah i have 3 different targets (monthly target) which change every year.

Year 2018

KPI 1: Monthly Value: 175

KPI 2: Monthly Value: 100

KPI 3: Monthly Value: 75

Year 2017

KPI 1: Monthly Value: 200

KPI 2: Monthly Value: 150

KPI 3: Monthly Value: 85

AND so on.

Do you have any idea how to proceed

Re: Create a dynamic Target Line

avkeep01 — Fri, 08 Jun 2018 11:29:16 GMT

Hi Yann,

Just look at my script below, and add an extra column in the inline table Named "KPI",

INLINE [

Year, Target, KPI

2018, 175, KPI1

2018, 100, KPI2

2018, 75, KPI3

2017, 200, KPI1

2017, 150, KPI2

2017, 85, KPI3

];

Target:

NOCONCATENATE LOAD

Year,

Target/[NoOfDays] AS Target,

KPI

RESIDENT temp01_Target;

DROP TABLE temp01_Target;

Then you can use the field KPI in your set analysis:

SUM({$<KPI={"KPI1"}>}Target)

Re: Create a dynamic Target Line

lessassy — Fri, 08 Jun 2018 11:58:20 GMT

It's changing but i don't have the right values...

Re: Create a dynamic Target Line

lessassy — Fri, 08 Jun 2018 11:59:45 GMT

THe target are multiplie by 4 or 5

Re: Create a dynamic Target Line

lessassy — Fri, 08 Jun 2018 12:01:43 GMT

Re: Create a dynamic Target Line

avkeep01 — Fri, 08 Jun 2018 12:09:11 GMT

You forgot to add KPI in you target table.

Target:

NOCONCATENATE LOAD

Year,

KPI,

Target/[NoOfDays] AS Target

RESIDENT temp01_Target;

DROP TABLE temp01_Target;

and just to be sure use

DROP FIELD Year FROM Target;

in the end.

Re: Create a dynamic Target Line

lessassy — Fri, 08 Jun 2018 12:15:03 GMT

There is an error

ANd here is my following script:

// NoOfDays

temp01_Target:

NOCONCATENATE LOAD

Year,

COUNT(Created) AS [NoOfDays]

RESIDENT MasterCalendar

GROUP BY Year;

// target figures

LEFT JOIN (temp01_Target) LOAD

Year,

Target*12 AS Target

INLINE [

Year, Target, KPI

2018, 120, LBTARGET

2018, 25, LETARGET

2018, 35, MTTRTARGET

2017, 300, LBTARGET

2017, 30, LETARGET

2017, 40, MTTRTARGET

2016, 600, LBTARGET

2016, 60, LETARGET

2016, 50, MTTRTARGET

2015, 300, LBTARGET

2015, 30, LETARGET

2015, 40, MTTRTARGET

];

Target:

NOCONCATENATE LOAD

Year,

Target/[NoOfDays] AS Target,

KPI

RESIDENT temp01_Target;

DROP TABLE temp01_Target;

LEFT JOIN (Target) LOAD

Year,

Created

RESIDENT MasterCalendar;

DROP FIELD Year FROM Target;

I don't know what is wrong

Re: Create a dynamic Target Line

avkeep01 — Fri, 08 Jun 2018 12:19:31 GMT

Sorry, I also forgot to add the KPI in the previous table:

// target figures

LEFT JOIN (temp01_Target) LOAD

Year,

KPI,

Target*12 AS Target

INLINE [

Year, Target, KPI

2018, 120, LBTARGET

2018, 25, LETARGET

2018, 35, MTTRTARGET

2017, 300, LBTARGET

2017, 30, LETARGET

2017, 40, MTTRTARGET

2016, 600, LBTARGET

2016, 60, LETARGET

2016, 50, MTTRTARGET

2015, 300, LBTARGET

2015, 30, LETARGET

2015, 40, MTTRTARGET

];

Re: Create a dynamic Target Line

lessassy — Fri, 08 Jun 2018 12:42:12 GMT

THe numbers are more clear but it's not the value i wrote unfortunately...

Re: Create a dynamic Target Line

avkeep01 — Fri, 08 Jun 2018 12:46:49 GMT

Hi Yann, that is the difficulty of making targets for months and weeks, not all months have the same number of days, and weeks can exist in more then 1 month. So thats why I calculated the target back to days and spread them over the year.

Otherwise you'll have to make 2 budgets, on per week, on per month and that per KPI.

Re: Create a dynamic Target Line

lessassy — Fri, 08 Jun 2018 14:10:59 GMT

I don't really understand what you are saying.

SO you calculated my monthly target (120) on a daily basis and spread it all over the year.

Because in the formula: one time you multiply it by 12 by doing Target x12 and the other time you do

Target/[NoOfDays]

I don't understant how multiplying or dividing a monthly target can work.

Sorry my english is not that great and i'm not clearly at ease with scripting and adavanced set analysis in data management

Re: Create a dynamic Target Line

avkeep01 — Fri, 08 Jun 2018 14:31:37 GMT

Hi Yann,

No problem, I'll try to illustrate it with an calculated example.

monthly target = 175, so multiplied by 12 months is your yearly target: 2.100.

Divided by 365 days for 2018 gives you 5,75 (rounded number). I've done that as follows: Target/[NoOfDays]

You can't simply divide by 365 because of leap years (366 days).

So right now you have a target of 5,75 per day. But Januari exists of 31 days: 178,36 (rounded) while Febraury exists of 28 days: 161,1 that causes different values per month.

Then the weeks:

When you set the target at 175 per month, you should it be shown in weeks? for example last week, week 22 had 4 days in May and 3 days in June. What part of the target should be shown in week 22? That is the problem of showing monthly targets per week.

Re: Create a dynamic Target Line

lessassy — Fri, 08 Jun 2018 14:42:47 GMT

I understand what you're saying thank you.

SO i have to expect some deviation.

But still here are my target and you can see that the target is very different in the chart. I choose LBTarget = 120.

The result is around 300 so idon't understand why

Here is the formula in the line chart property

Do you know where i did wrong.

Anyway thank you i reallly appreciate your help

Re: Create a dynamic Target Line

lessassy — Fri, 08 Jun 2018 14:44:16 GMT

Or maybe if ii want my target to be more accurate, if i put the 12 month instead of the year, would it be more accurate ?