Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Dynamic KPI selection

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.

KPIList.JPG

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

VariableUsage.JPG

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

1 Solution

Accepted Solutions
Highlighted
Partner
Partner

Re: Dynamic KPI selection

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.

Naamloos.png

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


With kind regards,

Ronald

View solution in original post

16 Replies
Highlighted
Partner
Partner

Re: Dynamic KPI selection

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

Highlighted
Creator
Creator

Re: Dynamic KPI selection

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

Highlighted

Re: Dynamic KPI selection

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?

Highlighted
Creator
Creator

Re: Dynamic KPI selection

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.

KPISelection.JPG

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

Highlighted

Re: Dynamic KPI selection

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

Highlighted
Creator
Creator

Re: Dynamic KPI selection

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

Highlighted

Re: Dynamic KPI selection

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

Highlighted
Creator
Creator

Re: Dynamic KPI selection

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.

Highlighted
Partner
Partner

Re: Dynamic KPI selection

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