Create a dynamic Target Line

[lessassy]

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

[lessassy]

[avkeep01]

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.

[lessassy]

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

[avkeep01]

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

];

[lessassy]

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

[avkeep01]

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.

[lessassy]

"

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.

"

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

[avkeep01]

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.

[lessassy]

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

[lessassy]

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 ?