Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Thank you Friends for helping me by giving your ideas.. thank you so much all
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)
Ravindraa,
Which of those two expressions uses the correct field names? Those two have different capitalizations which will be pointing towards differing fields