Row comparison

[sinanozdemir]

Hi all,

I have the below table:

Company	Amount
A	100
B	200
C	90
D	85

What I want to do is to compare A to other companies:

Company	Amount	Comparison
A	100	No Comparison Necessary
B	200	B is greater than A
C	90	C is lower than A
D	85	D is lower than A

I just wanted to see if there is an easy way to do this with or without creating additional dimensions/tables in the load script.

Straight or pivot table solutions are highly desirable.

Thank you.

[sinanozdemir]

Hi Sunny,

I also came up with a solution which is a bit different than yours, but would you mind looking at the attached document and my expressions?

It is simply comparing every company to company A within regions:

And here is the expression for folks who cannot open qvw files:

As far as I know, this expression works because if statement is used within a function so it makes it row level. I might be wrong. However, do you think this could be a solution as well?

Thank you again.

[sunny_talwar]

Hey Sinan -

Your method definitely works and it works perfectly it seems. But, I am still a newbie to be able to tell which method is better. I have another option for the new data you shared. This one doesn't use Aggregate. Personally, I try to minimize the use of aggregate function. With that said, I am not really sure if my expression with TOTAL is any better than the one you have, but since you asked for my suggestion, I am going to give you another expression and you can probably stress test them to see which one is a better option.

Existing Expression:

=If(RowNo() = 1, 'No Comparison',

  If(Sum(Aggr(if(Company <> 'A',Amount), Region, Company)) >

  Above(Sum(if(Company = 'A', Amount)),1,1), 'Greater than A', 'Lower than A'))

New Expression:

=If(Match(Company, 'A'), 'No Comparison',

  If(Sum(Total <Region> {<Company = {'A'}>}Amount) > Sum(Amount), 'Lower than A', 'Greater than A'))

Also attaching the qvw document for you to review.

HTH

Best,

Sunny

[sinanozdemir]

Thank you so much Sunny for your time.

Yours looks much easier to understand and read.

Thanks a lot again.

[sunny_talwar]

No problem at all

I am glad together we can get the best solution.