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

[MariaCruz1991]

Excuse me, is it possible to count the total of A, B, C, and place it within a KPI?, that is, have a kpi of the total of A=20, another of B=15, etc. If it is possible, could you tell me how?

Thanks

[stevedark]

Hi @MariaCruz1991 

If I understand correctly, you probably want a Text and Image object and a measure which concatenates the parts:

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

You might find that having a new table object and having three measures and then changing the font size to make it more like a KPI than a table might work better from a look and feel perspective.

If you are building a string, as above, you should probably also add num functions to tidy the figures.

Hope that makes sense.

Steve