how to get two max values

[Former Member]

Hi experts

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

aaa         500

bbb         400

ccc         300

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

sunil

[Former Member]

Hi sunil,

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

Regards

Kannan balamurugan

[Former Member]

Hi Sunil,

You can try the following also

The main report looks like

Now apply break on state and part of report looks like

Apply ranking on city based on sales revenue with top 2

Now the report looks like

Now apply sum on the sales revenue

Hope this is what you are lookinf for.

Regards

Arijit

[Former Member]

Hi

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 !!!

[Former Member]

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

Regards

Arijit

[vinay_lohakare5]

Hello,

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.

Thanks,

Vinay

[Former Member]

HI,

create

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

[Former Member]

hi

first of all thanks for reply, we are not able to write sql in OLAP reports.That thing is not avilabel in our infoview . so can u give any idea other than that??