topic Re: Dynamic KPI selection in QlikView

Dynamic KPI selection

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Dear community,

I would like to let the user decide which KPIs are displayed on one of my sheets.

The KPIs are selected in an input box, an optional list provides the name of the KPIs.

The problem I am facing is that the dashboard displays an average KPI for all companies and individuals KPIs for every single company (ManagementID).

The expressions and used variables for individual/averages values are different.

For the individual KPIs in the straight table it's just the chosen KPI itself, e.g.

$(eUmsatzrentabilität)

The average KPI expression, however, uses a different variable:

=Num(Avg({<Scope={'Scope Q2'}, [Management ID]>} Aggr($(eUmsatzrentabilitätManID),[Management ID])), '#,##%')

The variable is called

$(eUmsatzrentabilitätManID)

As you can see, the variables are always named in this fashion, the variable used for the averages is named like the variable for the individual KPIs + 'ManID'

To dynamically change the calculation of the individual KPIs I can just use $(eKennzahl1), $(eKennzahl2) in the straight table.

But how do I also adjust the expressions for the averages?

Can we use something like

=Num(Avg({<Scope={'Scope Q2'}, [Management ID]>} Aggr(

$(eKennzahl1&ManID)

,[Management ID])), '#,##%')

I will attach the sample file.

Thanks in advance.

Best regards,

Mats

Re: Dynamic KPI selection

RonaldDoes — Fri, 09 Jun 2017 08:56:16 GMT

Hi Mats,

Sorry if I'm missing something, but entering the KPI name into a variable doesn't seem very user-friendly to me.

Why don't you load all your KPI's into a data island (a table with no relations tot your data model) and have them choose a KPI in a list box (or multibox if you want to show 4 KPI's).

Pick up the selected KPI using GetFieldSelections and store your expressions for both individual as well as global averages into variables.

Would that work at all for you?

With kind regards,

Ronald

Re: Dynamic KPI selection

Anonymous — Fri, 09 Jun 2017 09:23:22 GMT

Hi Ronald,

You are right, it does not seem to be that user-friendly.

I didn't know about the option you suggested. Of course I am open to better solutions!

Can you tell me how it would work exactly?

I get the idea of creating a data island with available KPIs.

How would the variables for the individual and global averages look like?

I also haven't worked with GetFieldSelection before.

Thanks a lot!

Best regards,

Mats

Re: Dynamic KPI selection

sunny_talwar — Fri, 09 Jun 2017 09:27:29 GMT

I am not fully sure I understand where exactly are we looking to enter the KPI... can you give an example of input and the output you expect to see in your KPI?

Re: Dynamic KPI selection

Anonymous — Fri, 09 Jun 2017 09:39:36 GMT

Hi Sunny,

I think Ronald's solution would speed up the process of selecting the KPIs quite a lot.

Let's say I create the data island with KPIs and then select 4 KPIs in the multibox.

The expressions obviously have to adjust for every selection.

Does that make sense?

I am not sure how to use the GetFieldSelection and how the variables for individual/average KPIs would look like.

Thanks!

Best regards,

Mats

Re: Dynamic KPI selection

sunny_talwar — Fri, 09 Jun 2017 09:42:17 GMT

You can use data island or you can predefined list of values within your input box. But that is a separate matter, I am looking for few inputs I can enter and what will be the value based on those inputs in your KPI object

Re: Dynamic KPI selection

Anonymous — Fri, 09 Jun 2017 09:42:19 GMT

So I choose the KPI, e.g. "revenue" in the Multibox and QlikView takes the field selection and uses two variables for

a) the average
and
b) the individual value

Re: Dynamic KPI selection

sunny_talwar — Fri, 09 Jun 2017 09:43:59 GMT

You seem to have updated your sample, can you add the updated sample?

Re: Dynamic KPI selection

Anonymous — Fri, 09 Jun 2017 09:51:53 GMT

I just edited the labels to clarify what I would like to achieve.

I will have to create the KPI table (data island) to move forward.

I will post the update as soon as I am done.

Thanks for your patience.

Re: Dynamic KPI selection

RonaldDoes — Fri, 09 Jun 2017 10:40:27 GMT

Hi Mats,

Sorry for my slow reply.

If you have your example updated with the KPI table, please upload that, I'll be more than happy to apply my suggestion to see if that works for you.

Ronald

Re: Dynamic KPI selection

Anonymous — Fri, 09 Jun 2017 11:43:39 GMT

Thanks Ronald! I will get back to you soon

Re: Dynamic KPI selection

Anonymous — Fri, 09 Jun 2017 13:58:40 GMT

Hi,

I don't know if this makes it any more clear.

I just added a few KPIs to a table. I guess in order to select 4 different KPIs from the same pool I'll just load the same table 4 times.

Now what's displayed here for KPI 1 = eUmsatz ist just what I want to see. I select the KPI from the Multibox and the used variables in the KPI objects on the right adjust accordingly.

Please let me know if you have any further questions.

Thanks again.

Best regards,

Mats

Re: Dynamic KPI selection

RonaldDoes — Fri, 09 Jun 2017 18:30:06 GMT

Hi Mats,

I have taken the liberty of adding some text boxes with the "steps" behind my reasoning.

Apart from that (and enabling "Always one selected value" on your multibox (optional, but advisable) I have not changed anything.

Please let me know if this works for you at all.

With kind regards,

Ronald

Re: Dynamic KPI selection

Anonymous — Sat, 10 Jun 2017 08:39:21 GMT

Hi Ronald,

Thank you for taking the time to do this.

This looks very promising, I can now use the expression in the straight table.

Can you tell me how the according expression/variable for the average values looks like?

Best regards,

Mats

Re: Dynamic KPI selection

Anonymous — Sat, 10 Jun 2017 12:05:10 GMT

I think it should be something like this:

=Num(Avg({<Scope={'Scope Q2'}, [Management ID]>} Aggr($($(=GetFieldSelections(KPI)&ManID)),[Management ID])), '#,##%')

Re: Dynamic KPI selection

Anonymous — Sat, 10 Jun 2017 12:23:03 GMT

Ah, I got it, just had to add the ' ' around ManID. Thanks again for your help!

Clean and simple solution.

Re: Dynamic KPI selection

RonaldDoes — Sat, 10 Jun 2017 15:39:07 GMT

Hi Mats,

Great to hear it worked for you.

If =Num(Avg({<Scope={'Scope Q2'}, [Management ID]>} Aggr($($(=GetFieldSelections(KPI)&ManID)),[Management ID])), '#,##%') works for all your KPI's, that's fine.

If it doesn't, you could also store the "average" expression into eGesamtkapitalrentabilität_Average (so: KPI name & _Average). That way, you could have your KPI-textbox display $($(=GetFieldSelections(KPI)&'_Average')) and you would be able to set an expression for the KPI textbox that differs from the table.