cancel
Showing results for 
Search instead for 
Did you mean: 

Interesting: Top% of %GT 'Amount' display different records...

Former Member
0 Kudos

We created a formula KF of ABS ( %GT 'Amount' ), then we created a condition of Top% of this formula KF "ABS ( %GT 'Amount' )" and the percentage number Top% is a variable input by users.

Case 1: 100% as the input for Top%

All the records show up and totally there are three records, the percentage number for the three are 68.57%, 31.18%, and 0.25% respectively and the Overall Result row shows 100%.

Case 2: 1% - 68% as input value for Top%

Only 1st record of 68.57% shows up and the Overall Result row shows 68.57%.

Case 3: 69% - 99% as input value for Top%

1st record (68.57%) and 2nd record (31.18%) show up and the Overall Result row shows 99.75%.

Case 1 is easy to understand, but could anyone explain Case 2 and Case 3?

Thanks in advance and we will give you reward points for valuable answers!

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

In case 2, the row shown is the one which gives an Overall Result > 68%, which is the Top% value requested.

In case 3, the rows shown are the ones which give an Overall Result > 99%, which is the Top% value requested.

Hope this helps...

Bob

Former Member
0 Kudos

hi Bob,

You say "In case 2, the row shown is the one which gives an Overall Result > 68%, which is the Top% value requested.", but the input variable value ranges from 1% to 68%, let's say if the input value for Top% is 1%, then 2nd record of 31.18% should show up since it's greater than 1% based on your explanation, then could you answer why 2nd record fails to show up?

You say "In case 3, the rows shown are the ones which give an Overall Result > 99%, which is the Top% value requested.", but the input variable value ranges from 69% to 99%, could you elaborate how the Top% value is requested that this time 1st two records show up?

Sorry your answers are not persuasive.

Thanks!

Former Member
0 Kudos

Kevin,

Sorry that my first response was not clear enough. I'll try to do better...

The Top% function is used to retrieve the subset of rows that total at least the percentage specified. Obviously, you would want it to sort the numbers being evaluated in descending order first, otherwise you would not be retrieving the "top" ones.

In your case, that means that it is sorting by ABS ( GT% (Amount) ), then choosing the largest value(s) from the sorted list until it reaches the threshold you have specified.

In case 2, that means it chooses the largest value (68.75%) and stops there, since the cumulative total is > 68%, as specified.

In case 3, that means it chooses the largest value (68.75%) plus the next largest value (31.18%) and stops there, since the cumulative total (99.93%) is > 99%, as specified.

Persuaded?

Bob

Former Member
0 Kudos

hi Bob,

You say "In case 2, that means it chooses the largest value (68.75%) and stops there, since the cumulative total is > 68%, as specified.". Forget about 68% as the input value, how would you explain if the input value is 1% or 2%, then only 1st record (68.75%) shows up other than 2nd one (31.18%) which is also greater than 1% or 2% as the input value for top%?

Thanks

Former Member
0 Kudos

Yes, exactly!

Otherwise, you would not be getting the "top" values.

Maybe it would help to think of it in more generic terms. If I want to see the "Top 20%" of my customers based on Revenue, I would sort the list by Revenue (descending) then start accumulating revenue amounts until I reached a number that was 20% or greater than the total revenue for all customers. Then I would display the customers that were used to reach that value.

The same goes for your case. Whatever number is specified for Top N% is used to evaluate the key figure you chose (ABS(GT%(Amount))) in descending order until the a cumulated value >= N is reached.

Hope this helps...

Bob

Answers (0)