topic Re: Create a dynamic Target Line in QlikView

Create a dynamic Target Line

lessassy — Wed, 25 Nov 2020 16:16:04 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 ?

Here are my sample files attached.

Thank you

Re: Create a dynamic Target Line

sunny_talwar — Wed, 06 Jun 2018 14:25:35 GMT

Where is this target information stored?

Re: Create a dynamic Target Line

lessassy — Wed, 06 Jun 2018 14:46:46 GMT

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';

Re: Create a dynamic Target Line

sunny_talwar — Wed, 06 Jun 2018 14:53:21 GMT

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?

Re: Create a dynamic Target Line

lessassy — Wed, 06 Jun 2018 15:01:16 GMT

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 ?

Re: Create a dynamic Target Line

sunny_talwar — Wed, 06 Jun 2018 15:47:51 GMT

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

Re: Create a dynamic Target Line

lessassy — Wed, 06 Jun 2018 19:36:45 GMT

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....