topic Question with Straight table in QlikView

Question with Straight table

Wed, 03 Aug 2011 13:57:52 GMT

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

Re: Question with Straight table

martin — Wed, 03 Aug 2011 14:44:20 GMT

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

Question with Straight table

Wed, 03 Aug 2011 14:50:12 GMT

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

Re: Question with Straight table

Wed, 03 Aug 2011 14:50:23 GMT

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.

Re: Question with Straight table

Wed, 03 Aug 2011 15:00:33 GMT

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!

Question with Straight table

ToniKautto — Wed, 03 Aug 2011 15:05:48 GMT

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

Re: Question with Straight table

Wed, 03 Aug 2011 15:27:25 GMT

Thanks for the reply Toni.

Here I am attaching the file.

Thanks!

Re: Question with Straight table

johnw — Wed, 03 Aug 2011 20:04:24 GMT

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'))

Re: Question with Straight table

Wed, 03 Aug 2011 20:40:12 GMT

Yeah, Got it Thanks John.

Thanks all!