Top 3 Company Over 2 Years

[#1]

Hello Friends,

I have following table,

Company	Sales	Year
A	205	2006
A	301	2005
A	56	2004
B	257	2006
B	654	2005
B	147	2004
C	256	2006
C	65	2005
C	59	2004
D	598	2006
D	254	2005
D	10	2004

and I have to find top 2 company over past 2 years, I want an output table as

Comapany	Sales
B	911
D	852
A	506

please if anyone has any idea,help me out

[eduardo_sommer]

Hi

I created a qvw with a straight table, with Company as the Dimension and the expression

        =sum({$<Year={'$(=Max(Year))','$(=Max(Year)-1)'}>} Sales)

On the Dimension Limits, I set to show only te 2 largest values, with no Other.

See the attached qvw.

Eduardo

[its_anandrjs]

Hi Bhawna,

Did you check the result of the expression =sum({$<Year={vCurrentYear},Year={vLastYear}>}Sales)

you get only sum of last 2 years but not B,D,A as you discussed in the post for top3 for last 2 years did you try with the this expression

=if( Rank(Sum({<Year = {'>=$(=Max(Year-1)) <=$(=Max(Year))'} >} Sales)) <= 3, Sum({<Year = {'>=$(=Max(Year-1)) <=$(=Max(Year))'} >} Sales))

Which will give for last 2 years sales and for top 3 customers with ranking use.

Over all Robert expression is correct but not ranking the expression

Regards

[#4]

Thank you Friends for helping me by giving your ideas.. thank you so much all

[ravindraa]

Hi Robert Daitzman,

  I have also same requierment i am using your logic but i have used variables instead of direct year's like

vcurrentyear=year(this is my field)

vpreviousyear = max(year)-1

i am trying to write set analysis like below but i am not getting any value it is showing emty table if you know any suggession please give to me.

i have used below  two expression :

=sum({$<Year2={vcurrentyear},Year2={vpreviousyear}>}Sales2) .

=sum({<Year2={"$(=vCurrentYear)"},Year2={"$(=vpreviousyear)"}>}sales2)

[#6]

Ravindraa,

Which of those two expressions uses the correct field names?  Those two have different capitalizations which will be pointing towards differing fields