Question with Straight table

[#1]

Hi,

I have a requirement to add a column 'Rating' next to Sales.  Where rating should be Good if sales is greater than 800k. And rating should be Ok if Sales is between 600K-800K and Bad if Sales is <600K.

I already have City and Sales in the Straight table.  I am trying to add the column Rating using if condition in the expression, but I am finding it hard to make it work.

Any idea on this would be appreciated.

Thanks

[martin]

Hi,

You have to create an expression in your straight table like this :

if(Sales>800000, 'Good', if(Sales<600000, 'Bad', 'Ok'))

I consider your first expression nammed " Sales ".

Hope that helps you.

Martin

[#3]

Hi there

try a new expression similar to this:

if(Sales >  500, 'Very Good',

if(Sales >   50,      'Good',

if(Sales >    0,        'OK',

                    'Poor' // all the rest

)))

HtH

Roland

[#4]

Thanks for the reply Martin.

As you said I used:

if(Sum(SalesPrice)>800000, 'Good', if(Sum(SalesPrice)<600000, 'Bad', 'Ok'))

But it gives me result as below.  This looks wrong. 

[#5]

Thanks for the reply Roland. 

Let me rephrase it.  I have three column. 

1. Region

2. Sum(Sales)

3. Rating according to the Sum(Sales)

I tried

if(Sum(SalesPrice) >  800000, 'Good',

if(Sum(SalesPrice) <   600000,      'Bad',

                    'Ok' // all the rest

))

But it gives me 'Good' for all Regions.

But I should get

MidWest- Bad

NorthEast- Ok

SouthEast- Good

SouthWest Bad

West - Ok

Guide me on this!

Thanks!

[ToniKautto]

Please attach a sample application so that your data structure could also be evaluated.

[#7]

Thanks for the reply Toni.

Here I am attaching the file.

Thanks!

[johnw · Accepted solution]

Have a look at your Sales column:

=num(sum(if(YTD, SalesPrice)), '$#,##0;($#,##0)')

Now, how is your rating supposed to work if it's only doing a sum(SalesPrice)?  It's ignoring the YTD flag.  In any case, you don't need to restate the whole expression.  Just refer to the sales column directly by its label, like I believe Martin and Roland were saying.

if(Sales > 800000, 'Good', if(Sales < 600000, 'Bad', 'Ok'))

[#9]

Yeah, Got it Thanks John.

Thanks all!