topic Re: Rank Filter and Selection Filter in Set Expression in App Development

Rank Filter and Selection Filter in Set Expression

Wed, 27 Jul 2016 16:47:36 GMT

I am working with incident data in the following format where each row details one registered incident:

ID	Priority	Component
1	Severe	Till
2	Moderate	Software
...	Critical	Hardware

I attach some sample data.

I have a list box/field that allows for the user to select the 'Priority' level. I am trying to create a KPI that does the following: Counts the number of incidences - i.e. Count(ID) - only for the priority level selected (or all if none selected) and only for the top 5 components by Count(ID).

I have attempted the following with no joy:

Count({1<[Priority] = P([Priority])>*1<[Component] = {"=Rank(Count([ID]))<=5"}>} [ID])

Any help would be greatly appreciated.

Kind regards,

Re: Rank Filter and Selection Filter in Set Expression

sunny_talwar — Wed, 27 Jul 2016 17:38:36 GMT

May be this:

Count({1<Ccomponent = {"=Num(Rank(Count(ID), 4, 1)) <= 5"}>} ID)

Re: Rank Filter and Selection Filter in Set Expression

Wed, 27 Jul 2016 21:06:43 GMT

This doesn't seem to be what I'm looking for. I'm also not sure what Num does in this context.

To be clear what I want is a count of the ID's for the top 5 components that will only vary if a selection is made on the Priority level. If any other selection is made I want nothing to change.

Re: Rank Filter and Selection Filter in Set Expression

sunny_talwar — Wed, 27 Jul 2016 22:32:47 GMT

Would you be able to elaborate as to what you are trying to get

Re: Rank Filter and Selection Filter in Set Expression

Thu, 28 Jul 2016 08:05:36 GMT

"what I want is a count of the ID's for the top 5 components that will only vary if a selection is made on the Priority level. If any other selection is made I want nothing to change." This what I am trying to get.

I would like the result to filter down if a selection is made in the Priority field but not if a selection is made in the component field. And I would like the result to always show the count from the top 5 components within the selected Priority level.

Re: Rank Filter and Selection Filter in Set Expression

sunny_talwar — Thu, 28 Jul 2016 08:37:47 GMT

I think it would be better if you can point out the expected number based on selection in different Priority level selection. If I have the numbers you are trying to get, I might be able to help you better

Re: Rank Filter and Selection Filter in Set Expression

Thu, 28 Jul 2016 08:45:07 GMT

No Priority selection: 280

Priority 1: 42

Priority 3: 190

Priority 2 + Priority 3: 236

And if any component or set of components is selected these numbers should not change

Re: Rank Filter and Selection Filter in Set Expression

sunny_talwar — Thu, 28 Jul 2016 10:38:05 GMT

Check out this expression:

=Count({1<Ccomponent = {"=Num(Rank(Count(ID), 3, 1)) <= 5"}, Priority = p(Priority)>} ID)

Re: Rank Filter and Selection Filter in Set Expression

Thu, 28 Jul 2016 10:57:04 GMT

Hi Sunny,

Unfortunately this doesn't seem to work for me.

If I make a selection on one of the components it updates the total. I do not want this to happen. I just want the total to update if Priority is changed. See below. The total should not change for the bottom KPI.

Re: Rank Filter and Selection Filter in Set Expression

sunny_talwar — Thu, 28 Jul 2016 11:40:33 GMT

How about this?

=Count({1<Ccomponent = {"=Num(Rank(Count({1<Priority = p(Priority)>}ID), 3, 1)) <= 5"}, Priority = p(Priority)>} ID)

Re: Rank Filter and Selection Filter in Set Expression

Thu, 28 Jul 2016 13:15:06 GMT

Sunny, thank you for your continued effort on this! And you are so close with this answer. It is now almost doing what I want with only one small problem...

The output of the function P(Priority) seems to alter if a selection on the component field affects the list of possible Priorities i.e. one of the priorities turn dark grey.

Apart from this it is working perfecly.

Re: Rank Filter and Selection Filter in Set Expression

sunny_talwar — Thu, 28 Jul 2016 15:07:41 GMT

How about this

=Count({1<Ccomponent = {"=Num(Rank(Count({1<Priority = $::Priority>}ID), 3, 1)) <= 5"}, Priority = $::Priority>} ID)

Re: Rank Filter and Selection Filter in Set Expression

Thu, 28 Jul 2016 15:11:15 GMT

You have done it! That is heroic effort!

I may be worth a small explanation explaining how this works for anyone visiting later...

Thank you very much!