Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

16 Replies
Anonymous
Not applicable
Author

Thanks Ronald! I will get back to you soon

Anonymous
Not applicable
Author

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

RonaldDoes
Partner - Creator III
Partner - Creator III

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

Anonymous
Not applicable
Author

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

Anonymous
Not applicable
Author

I think it should be something like this:

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

Anonymous
Not applicable
Author

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

Clean and simple solution.

RonaldDoes
Partner - Creator III
Partner - Creator III

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.