I am stuck with the problem .looks easy but i unable to solve it .
Using BoXi 3.1 webi Olap cubes.
here my data looks like this
country city sales revenue
aaa xxx 100
aaa yyy 200
aaa zzz 300
bbb ddd 100
bbb sss 200
bbb ppp 200
ccc qqq 300
what i would like to see my table is like
country sales revenue
i want the first max and second max from country aaa and sum it up. In the same way for the country bbb here lies the problem if both the values are max then i would like to aggregate both the values and have to show it as 400 .
Much appreciated with inputs.
Thanks and regards
Use the Rank function to get the top Sales revenue per Country.
Create a variable to sum the sales revenue with rank 1 and 2 for each country.
If the rank is same then take the average or only one value.
In you example for country bbb the average for 200 and 200 will be 200, so the sum will be 200+300=500 and not 400.
table test (country varchar(100), city varchar(100), Salesrevenue int
insert the data which is mentioned by you in your post and run the following query. the results is as excpected by you.Let me know if you need any more information on the same
select country, sum(salesrevenue) from (select country,salesrevenue ,rank() over (PARTITION BY COUNTRY order by salesrevenue DESC ) AS RK from test b )C WHERE RK<=2 group by country
First of all thanks for ur reply, I think u didnt properly understood . i want to show the data only on country.with the sum of two max values. when we use rank then we will get max value.but what if the values are same can u aggregate it ?? i am thinking abt the solution for that !!!
Even if the values are same, if they are max values, then they will be shown after applying the ranking. And the aggregated value will be shown by sum function as well.
Thne you can suppress the city column. The rpeort looks like this before suppressing the city column. Note that the state Illinois has same values for Chicago and Minneapolis and they are aggregated.
Now you suppress the city column to get your desired result like this
IT can be even better formatted by removing the break header and break footer and addming table header
I took ur example data and produced the output you want.
Gothrough the screen shot.
Create one variable called rank
Place that in Table.
right click the rank column and filter the values for 1 and 2.
now remove the city column
Now as expected output has came.
If you want to hide the rank ,do colouring white and hide the rank