Categories
sorting on measure column in a pivot table

Hi All,
I need to sort a pivot table based on a measure column.
Measure Values | Grand Total | |||
rank | 3/11 | 3/12 | 3/13 | |
2 | 12 | 10 | 22 | 44 |
1 | 10 | 20 | 10 | 40 |
3 | 15 | 10 | 25 | 50 |
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.
Answers
-
one more update.even i shall remove / hide rank column but the records should be displayed as per grand total in ascending order.
0 -
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)
0 -
Hello,
Lets check this example, which so similar as your requirements
1.Criteria section:
2.Formula by each Column:
3. Layout
Kind Regards,
0 -
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.
0 -
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 rep 11-Mar-16 12-Mar 13-Mar Revenue Quantity Revenue Quantity Revenue Quantity 2 Sam 1200 5 1000 10 2200 5 4400 1 Chander 1000 7 2000 7 1000 9 4000 3 Karthik 1500 6 1000 4 2500 13 5000 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 rep 11-Mar-16 12-Mar 13-Mar Revenue Quantity Revenue Quantity Revenue Quantity 1 Chander 1000 7 2000 7 1000 9 2 Sam 1200 5 1000 10 2200 5 3 Karthik 1500 6 1000 4 2500 13 0 -
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
0 -
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
0 -
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:
0 -
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,
0 -
Hi Cesar,
This worked fine.Thanks a lot.
Thanks,
Vengatesh.
0