Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Tell us which business and trade publications you read most regularly: RESPOND NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
124psu
Creator II
Creator II

dynamically change KPI

I'm working to configure my set analysis so that I can see the max dimension based on revenue. 

Example - I have 3 charts and would like to create 3 corresponding KPI's

1) Department by Sales

2) Client by Sales

3) Vendors by Sales

3 KPIs will be going against these 3 charts. When a user selects a specific year or multiple years, the KPI should update so that it takes that into account. I'd like to get the MAX revenue and return that specific string (dept, client, vendor). This way, when they filter on a year they'll see the max string for those 3. 

Labels (4)
1 Solution

Accepted Solutions
Rodj
Luminary Alumni
Luminary Alumni

If I understand correctly you want to return the  Department / Client / Vendor with the highest revenue with the result filtered by whatever other selections against year, product etc might be made.

So there's probably a couple of ways you can do this, but I thinking combining the Rank function into some set analysis might be the most efficient, something like this for each of your KPIs:

Only({$ <Dept = {"=Rank(Sum({$<Dept = >}Rev)) < 2"}>} Department)

This is basically aggregating the only department value that has a rank of sum(revenue) < 2. Depending on your data you'll probably need to play with this a little for your circumstances and to adopt it to Client, Vendor etc.

The "$" symbol in the set is telling it to take into account any selections made elsewhere in the associative model. The Only aggregation is handy when you want to aggregate by a single value.

Hopefully that sets you on the right track.

 

View solution in original post

4 Replies
Rodj
Luminary Alumni
Luminary Alumni

If I understand correctly you want to return the  Department / Client / Vendor with the highest revenue with the result filtered by whatever other selections against year, product etc might be made.

So there's probably a couple of ways you can do this, but I thinking combining the Rank function into some set analysis might be the most efficient, something like this for each of your KPIs:

Only({$ <Dept = {"=Rank(Sum({$<Dept = >}Rev)) < 2"}>} Department)

This is basically aggregating the only department value that has a rank of sum(revenue) < 2. Depending on your data you'll probably need to play with this a little for your circumstances and to adopt it to Client, Vendor etc.

The "$" symbol in the set is telling it to take into account any selections made elsewhere in the associative model. The Only aggregation is handy when you want to aggregate by a single value.

Hopefully that sets you on the right track.

 

124psu
Creator II
Creator II
Author

Awesome stuff!! This is exactly what I was looking for!! Thank you Qlik Angel (Rodj). 

Rodj
Luminary Alumni
Luminary Alumni

You're most welcome.

anushahegde
Contributor III
Contributor III

Could you please help me to understand why did you write <2 in the rank function? Why not other numbers? What does <2 do here?