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 ?

Here are my sample files attached.

Thank you

[sunny_talwar]

Where is this target information stored?

[lessassy]

It's on the attached files !

(But the main issue is that i have other KPIs as well.

ANd to always copie hundreds on columns and lines coul be a bit repetitive. !)

Is it possible ?

Thanks ?

My script for date and time is this:

SET TimeFormat='hh:mm:ss';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY hh:mm';

[sunny_talwar]

But the main issue is that i have other KPIs as well.

ANd to always copie hundreds on columns and lines coul be a bit repetitive. !

So, are you looking to not maintain this as a separate file? I mean you need this info from somewhere, how do you intend to get it if it isn't stored in an Excel file?

[lessassy]

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 ?

[sunny_talwar]

So, user will input this value? For just a single month or multiple months?

[lessassy]

THe user will have to input the target only once.

At the begining of the current year, he has all the target for each category.

And the target (whether it's week or month) never change.

Do you have a type of formula for this ?

Or ifyou don't, can u tell me how do u proceed for every month ?

Because i have tried before but i never suceed....