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

[avkeep01]

Can you attach the QVW then I can test my script before sending you incorrect lines of codes. Is probably more efficient

[lessassy]

Here is the qvw file !

Thanks a lot

[avkeep01]

The FinalTarget table exists 2 times in your script, 1 on the Target sheet and 1 on the FinalTarget sheet. The last one can be deleted.

Then, the values for the future months are 0 and in your presentation sheet at the properties of the graph. you dont have the "supress zero values" checked, so the line will still be shown for the future months. In the example I unchecked that box.