Calculate the borders of an ABC analysis

[#1]

Hello,

I am trying to calculate the border values of the classes in a dynamic ABC - analysis. But the problem is I need to perform this calculation in a text box, so the regular ABC - analysis stuff (sorting in a table and the calculate the rangesum) doesn't work here.

To give an example: I have a table with revenue values per customer. My Classes are A<=80%, B<= 95%, C>95%

Customer | Revenue | Class

CUST1 7.000 A

CUST2 5.000 A

CUST2 2.000 B

CUST3 700 C

CUST4 300 C

The result of the calculation for the class A should be 5.000 because it is the last value that is included in the class A.

The result of the calculation for the class B should be 2.000 because it is the last value that is included in the class B.

Is there any possibility to perform this class - border calculation within a text box? Is there any statistical function in QlikView that is able to handle this question?

Thanks for your help. I am looking forward to your suggestions.

Cheers,

Gerald

[johnw]

So... what do you want to appear in the text box? You just want a text box with 5.000 in it for A? This seems to work:

=firstsortedvalue({<Class={'A'}>} Revenue,-Sequence)

See attached.

You may notice the Sequence field. I can't think of a way to know which record is the last one for a given class without actually giving the records some sort of sequence. I assign the field using recno() as I load in the data.

Or is the sort order by Revenue descending? If so:

=firstsortedvalue({<Class={'A'}>} Revenue,Revenue)

Or is Class not even a field, and you need to calculate it on the fly? I guess I'm just not following what your data looks like and what you want to see. Feel free to modify my example to show what your data actually looks like and explain what you want to see.