Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Don't miss the upcoming Q&A with Qlik session on Qlik Application Automation on November 16th! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jpjust
Creator III
Creator III

Dropdown list to dynamic Kpi

All,

I have an  table as below.

UserName          Lastusedindays

Joseph                     25

Tim                            40

Joe                             50

Mary                          30

Mark                          40

I have an KPI that shows number for "More than 30 days". In the case of table above the KPI would show "3"

Now my requirement is , I have to supply an dropdown kind of interface so user can pick the "more than" for eg., "more than 25 days". In this case the kpi should dynamically change to 6.

Can you please let me know how to achieve it?

Thanks

1 Solution

Accepted Solutions
jwjackso
Specialist II
Specialist II

In your KPI,

SUM(If(IF(ISNULL(Today() -[User Access Last Used]) and ISNULL(Today() -[LastUsedEU]),0,
If(ISNULL(Today() -[User Access Last Used]),Today() - [LastUsedEU],
If(Today() -[User Access Last Used] < Today() - [LastUsedEU],Today() -[User Access Last Used],
IF (Today() -[LastUsedEU] < Today() - [User Access Last Used],Today() -[LastUsedEU],
If(ISNULL(Today() -[LastUsedEU]),Today() -[User Access Last Used],
If(Today() -[User Access Last Used] = Today() - [LastUsedEU],Today() -[User Access Last Used]
))))
)) >$(vDD),1,0))

 

Add the expression for lastuseddays to your load script, then you KPI becomes much simpler

Count({<Lastusedindays={">$(vDD)"}>}UserName)

 

View solution in original post

4 Replies
jwjackso
Specialist II
Specialist II

You can use the Variable Input Control to create a dropdown list that is assigned to a Variable that you create.  For example if you create variable vDD and use it in the Variable Input Control, you can then create a formula using Set Analysis  in your KPI

=Count({<Lastusedindays={">$(vDD)"}>}UserName)

 

jpjust
Creator III
Creator III
Author

Thanks so much for your time, I almost understand. I looked into the Variable Input control properties. Now having expression as below in my case, unsure how to connect these to variable -> Variable Input control -> KPI

Can you please provide me with some suggestions?

Below is my KPI expression that shows count of users more than 45 days.

SUM(If(IF(ISNULL(Today() -[User Access Last Used]) and ISNULL(Today() -[LastUsedEU]),0,
If(ISNULL(Today() -[User Access Last Used]),Today() - [LastUsedEU],
If(Today() -[User Access Last Used] < Today() - [LastUsedEU],Today() -[User Access Last Used],
IF (Today() -[LastUsedEU] < Today() - [User Access Last Used],Today() -[LastUsedEU],
If(ISNULL(Today() -[LastUsedEU]),Today() -[User Access Last Used],
If(Today() -[User Access Last Used] = Today() - [LastUsedEU],Today() -[User Access Last Used]
))))
)) >45,1,0))

Below is my expression for lastuseddays which returns values such as 25,50,60 etc.,

=If(ISNULL(Today() -[User Access Last Used]) and ISNULL(Today() -[LastUsedEU]),'Never',
If(ISNULL(Today() -[User Access Last Used]),Today() - [LastUsedEU],
If(Today() -[User Access Last Used] < Today() - [LastUsedEU],Today() -[User Access Last Used],
IF (Today() -[LastUsedEU] < Today() - [User Access Last Used],Today() -[LastUsedEU],
If(ISNULL(Today() -[LastUsedEU]),Today() -[User Access Last Used],
If(Today() -[User Access Last Used] = Today() - [LastUsedEU],Today() -[User Access Last Used]
))))
))

 

 

jwjackso
Specialist II
Specialist II

In your KPI,

SUM(If(IF(ISNULL(Today() -[User Access Last Used]) and ISNULL(Today() -[LastUsedEU]),0,
If(ISNULL(Today() -[User Access Last Used]),Today() - [LastUsedEU],
If(Today() -[User Access Last Used] < Today() - [LastUsedEU],Today() -[User Access Last Used],
IF (Today() -[LastUsedEU] < Today() - [User Access Last Used],Today() -[LastUsedEU],
If(ISNULL(Today() -[LastUsedEU]),Today() -[User Access Last Used],
If(Today() -[User Access Last Used] = Today() - [LastUsedEU],Today() -[User Access Last Used]
))))
)) >$(vDD),1,0))

 

Add the expression for lastuseddays to your load script, then you KPI becomes much simpler

Count({<Lastusedindays={">$(vDD)"}>}UserName)

 

View solution in original post

jpjust
Creator III
Creator III
Author

Perfect! That worked. Thanks much for your help