Where is the error in this formula?

[Nemo1]

Hello... so I want a formula that classifies a lists of stores in A,B,C. The first 10% of the stores should be A, the next 20% should be B and then C

I created a row in my table, which enumarates the stores, the formula I used in that row is NoRow().

Here is the formula:

if(RowNo()/Count(RowNo()) <= 0.1, 'A',

    if(RowNo()/Count(RowNo()) <= 0.3, 'B',

        'C'

    )

)

it is not working out for me... what could i be doing wrng?

thanks

[stevedark · Accepted solution]

Hi @Nemo1 

What order do you want the stores in, is it based on sales, location or alphabetically?

Once a store has been classified, should it change that classification based on the current selections?

If you were to do it taking current selections into account, based on Sales, the expression would be something like:

if(rank(TOTAL sum(Sales),2,1)/count(TOTAL DISTINCT StoreNo) <= 0.1, 'A',
if(rank(TOTAL sum(Sales),2,1)/count(TOTAL DISTINCT StoreNo) <= 0.3, 'B', 'C'))

Test data for this created with this load script:

SalesData:
LOAD
num(RowNo(), '000000') as StoreNo,
floor(Rand()*100000) as Sales
AUTOGENERATE(20000);

I've selected just the last ten stores, so you can see multiple categories in the screengrab.

If you wanted to catagorise the stores so they are permanently in their category regardless of selections (so you could select all category A, for example) then you would need to do similar calculations in the load script, using a GROUP BY statement and a RESIDENT load.

Let me know if that is the case.

Hope that helps.

Steve

[Nemo1]

The stores are ordered according to their sales.

Yes, once the classification should change according to selections/filters.

I tried the formula you wrote, but it classified all my stores as "C".. idk why? 

thanks

[stevedark]

Hi @Nemo1 

You will need to debug by creating a table that has all the constituent parts of the expression.

So, have the following columms:

StoreNo as a dimension
sum(Sales)  (or whatever your sales calc is)
rank(TOTAL sum(Sales),2,1)   (should be numbers 1 to 200)
count(TOTAL DISTINCT StoreNo)    (should be the same on all rows)
rank(TOTAL sum(Sales),2,1)/count(TOTAL DISTINCT StoreNo)  (as a %)

Finally, have the actual A, B, C expression.

Hopefully by seeing all the values broken out like that you should be able to see what is going wrong.

Perhaps you could share a screenshot of that table, if you can't figure out what needs changing - with any sensitive details obfuscated of course.

Steve