Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

sorting on measure column in a pivot table

Received Response
293
Views
10
Comments
Rank 2 - Community Beginner

Hi All,

I need to sort a pivot table based on a measure column.

   

Measure ValuesGrand Total
rank 3/113/12 3/13
212102244
110201040
315102550

I already tried below mentioned approach, but it is not working.

1) Make a duplicate layer of themeasure column.

2) Move the duplicate layer to the far left in the Rows section.

3) Apply the desired sort order.

4) Hide the duplicate layer column.

I need to sort the rank column in ascending order.

Can some one please let me know if there is any other approach to resolve this.

Thanks,

Vengatesh.

Welcome!

It looks like you're new here. Sign in or register to get started.

Answers

  • Rank 2 - Community Beginner

    one more update.even i shall remove / hide rank column but the records should be displayed as per grand total in ascending order.

  • Rank 8 - Analytics Strategist

    The way you've drawn your pivot you've got Rank as an attribute ... seems you aren't giving us the other dimension (time and ???)

    I did no special moves here (Year, Sales Rep Name, Revenue and RANK(Revenue)) -- worked by clicking the up/down sorting arrows (the default sort was the Sales Rep Name vertically)

    Capture.PNG

  • Rank 6 - Analytics Lead

    Hello,

    Lets check this example, which so similar as your requirements

    Rank1.png

    1.Criteria section:

    Rank2.png

    2.Formula by each Column:


          Period.png

            MontoFuncional.png

             RankindOrdenFormula.png

             RankingForumla.png

              Vendor.png

                Vendor2.png

    3. Layout

                     Structure.png

    Kind Regards,

  • Rank 2 - Community Beginner

    Hi Thomas,

    Thanks for input but I would need report as mentioned below in my comments not as Individual datewise , rather it would on dimension say BY Sales rep.

    Thanks,

    Vengatesh.

  • Rank 2 - Community Beginner

    Hi Cesar ,

    Thanks for that extensive screenshots. I have added the details as  below as per requirement. The Grand Total column is not needed.

       

    Measure Values Grand Total
    Rank(Revenue)Sales rep11-Mar-1612-Mar13-Mar
    RevenueQuantityRevenueQuantityRevenueQuantity
    2Sam12005100010220054400
    1Chander1000720007100094000
    3Karthik15006100042500135000

    Required Format:

    The Rank would be calculated on sum of all revenues for each representative and not by Date wise

       

    Measure Values
    Rank(Revenue)Sales rep11-Mar-1612-Mar13-Mar
    RevenueQuantityRevenueQuantityRevenueQuantity
    1Chander100072000710009
    2Sam1200510001022005
    3Karthik1500610004250013
  • Rank 6 - Analytics Lead

    Hello,

    You could create a measure sum( by "sales reps") this.measure helps you to do a rank after this( check the order by as my.response with.screenshot),  and finally it you could hide your.column go into criteria tab.properties.column and mark the hidden.property.

    Kind regards

  • Rank 2 - Community Beginner

    Hi ,

    Can you tell me how would I create measure  sum(By sales Rep) . Also I could not see your lates screen shot. could you pls check and add

  • Rank 8 - Analytics Strategist

    RANK(SUM(fact by dimension) will force the granularity of the calculation ... this example ignores the year ... so all values by sales rep are summed, then the rank is applied:Capture.PNG

  • Rank 6 - Analytics Lead

    1. Go oracle bi answers.

    2. Edit your "analysis" object.

    3. Create.a.column, and put the.mouse over the column, edit your formula and write the.code sum( revenue by sales reps)

    4. In the criteria tab, put the mouse over the column and you.coould.see.a.propertie order by, set up the order by to this.column

    5. Again put the mouse.over the.column and click.on column property, there and mark.with check the hidden property.

    Kind regards,

  • Rank 2 - Community Beginner

    Hi Cesar,

    This worked fine.Thanks a lot.

    Thanks,

    Vengatesh.

Welcome!

It looks like you're new here. Sign in or register to get started.