Creating new column for a category based on to sales rank

[ksharpes]

Hello,

I have a large amount of data for which I wish to assign a category of Class A,B or C.

The classification of this category will be based on Sales rank.

Class A will be the lines which are in the Top 80%

Class B will be the lines that are in the next 15%

Class C will be the remaining 5%

Also the data will be split by another column, in this case Country.

Each countries data will have its own classification.

I can pull the data together into a single table, but what would be the nest way of classifying from this point?

In excel I would sort by country then by value, then apply a percentage to it. then i would add an additional column which has a cumulative total percentage for each country, then apply the above classification. If i was to do this in Qlik how can i do this?

[benvatvandata]

Is your end goal to have something similar to the image attached (I'm using a formula for the Category column)?

[ksharpes]

Hello,

Many thanks for the reply, end result would look more like below.

Formulas in excel are as follow:

Value/Total Country Value =B3/SUMIF($A$3:$A$22,A3,$B$3:$B$22)

Cumulative total =SUMIF($A$3:$A3,A3,$C$3:$C3)

Category =IF(D3<0.8,"Class A",IF(D3<0.95,"Class B","Class C"))

The only column I care about is the Category Column the rest can be dropped after.

Country	Value	Value/Total Country Value	Cumulative total	Category
CA	150	57%	57%	Class A
CA	45	17%	74%	Class A
CA	19	7%	82%	Class B
CA	17	6%	88%	Class B
CA	16	6%	94%	Class B
CA	15	6%	100%	Class C
UK	200	46%	46%	Class A
UK	80	19%	65%	Class A
UK	75	17%	82%	Class B
UK	35	8%	90%	Class B
UK	15	3%	94%	Class B
UK	11	3%	96%	Class C
UK	9	2%	98%	Class C
UK	7	2%	100%	Class C
USA	100	74%	74%	Class A
USA	25	18%	92%	Class B
USA	5	4%	96%	Class C
USA	3	2%	98%	Class C
USA	2	1%	99%	Class C
USA	1	1%	100%	Class C

[benvatvandata]

Depending on what your dimensions are, the Category formula would be something like:

If(rangesum( above(sum(Value)/sum(total <Country> Value),0,Rowno()))<.8, 'Class A', if(rangesum( above(sum(Value)/sum(total <Country> Value),0,Rowno()))<.95,'Class B', 'Class C'))

In my previous example I had Country and Order as the dimensions, so I need sort by Country, then sort the Order dimension by an expression: '=sum(Value)'. (descending)

Otherwise if you have Country and Value as the Dimensions, you can sort by Country (descending), then Value (descending).

The sorting seems to be pretty essential for the Expression above to work. 

- Ben

[ksharpes]

Hi, many thanks for your answer.

The expression works in i add this in to a table within an app, however if i am trying to do this within the Data load editor it does not work.

The intention of this, is to have the category defined as part of the original data as the categorizations should not change, apologies if this was not clear.