7 Replies Latest reply: May 17, 2012 2:21 PM by Kannan Balamurugan RSS

how to get two max values

sunil kumar
Currently Being Moderated

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

  • Re: how to get two max values
    Vinay Lohakare
    Currently Being Moderated

    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

    • Re: how to get two max values
      DIVYA P
      Currently Being Moderated

       

      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

  • Re: how to get two max values
    Arijit Kole
    Currently Being Moderated

    Hi Sunil,

     

    You can try the following also

     

    The main report looks like

     

    1.jpg

     

    Now apply break on state and part of report looks like

     

    2.jpg

     

    Apply ranking on city based on sales revenue with top 2

     

    3.jpg

     

    Now the report looks like

     

    4.jpg

     

    Now apply sum on the sales revenue

     

    5.jpg

     

     

    Hope this is what you are lookinf for.

     

     

    Regards

    Arijit

    • Re: how to get two max values
      sunil kumar
      Currently Being Moderated

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

      • Re: how to get two max values
        Arijit Kole
        Currently Being Moderated

        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.

         

        1.jpg

         

         

        Now you suppress the city column to get your desired result like this

         

        2.jpg

         

        IT can be even better formatted by removing the break header and break footer and addming table header

         

        3.jpg

         

         

        Regards

        Arijit

  • Re: how to get two max values
    Kannan Balamurugan
    Currently Being Moderated

    Hi sunil,

     

    I took ur example data and produced the output you want.

    Gothrough the screen shot.

     

    1.jpg

    Create one variable called rank

    2.jpg

    Place that in Table.

    3.jpg

    right click the rank column and filter the values for 1 and 2.

    4.jpg

    now remove the city column

    5.png

    Now as expected  output  has came.

    If you want to hide the rank ,do colouring white and hide the rank

    6.png

     

     

     

    Regards

    Kannan balamurugan

Actions