Skip 0 values before making the calculated dimension

[Anonymous]

Hello,

I  have the following calculated dimension:

=aggr(if(rank(sum(sales),4)<0.1*count(total distinct customer), '0-10%',

if(rank(sum(sales),4)<0.2*count(total distinct customer), '10-20%',

if(rank(sum(sales),4)<0.3*count(total distinct customer), '20-30%',

if(rank(sum(sales),4)<0.4*count(total distinct customer), '30-40%',

if(rank(sum(sales),4)<0.5*count(total distinct customer), '40-50%',

if(rank(sum(sales),4)<0.6*count(total distinct customer), '50-60%',

if(rank(sum(sales),4)<0.7*count(total distinct customer), '60-70%',

if(rank(sum(sales),4)<0.8*count(total distinct customer), '70-80%',

if(rank(sum(sales),4)<0.9*count(total distinct customer), '80-90%'

, '90-100%'))))))))),customer).

I have Rows with 0 value, however i need them for the qwd.

¿it is posible to eliminate the values with sum 0?

[giakoum]

You could add another if I guess.

But why don't you use the class function?

[Anonymous]

i tried it i could not do it.

If you can help me  i would appreciate it.

[giakoum]

=if(sum(sales)=0, null(),

aggr(if(rank(sum(sales),4)<0.1*count(total distinct customer), '0-10%',

if(rank(sum(sales),4)<0.2*count(total distinct customer), '10-20%',

if(rank(sum(sales),4)<0.3*count(total distinct customer), '20-30%',

if(rank(sum(sales),4)<0.4*count(total distinct customer), '30-40%',

if(rank(sum(sales),4)<0.5*count(total distinct customer), '40-50%',

if(rank(sum(sales),4)<0.6*count(total distinct customer), '50-60%',

if(rank(sum(sales),4)<0.7*count(total distinct customer), '60-70%',

if(rank(sum(sales),4)<0.8*count(total distinct customer), '70-80%',

if(rank(sum(sales),4)<0.9*count(total distinct customer), '80-90%'

, '90-100%'))))))))),customer)

)

and check suppress when value is null

[Anonymous]

It gives me Allocated memory exceded

[Anonymous]

I do not know what i have touched but with the formula you game me it gives this table

As you can see from 40% to 90% have dissapeared, however i would like to have the formula only with data not 0 and null so it doesn´t dissapeare those 'ranges'.

[giakoum]

my mistake, replace sum(sales) to aggr(sum(sales), customer)

but I really suggest that you use class

[Anonymous]

where should it be that customer? it is giving problems.

how could it be done this with class?

class(client,10)?? i do not understand this function

[giakoum]

Please post a sample app

[Anil_Babu_Samineni]

Try something like below

=Replace(Aggr(Class(Sum(Sales)*100, 10), customer), '<= x <', ' - ')