nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

sorting on measure column in a pivot table

Vengatesh

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.

Find more posts tagged with

Accepted answers

All comments

Vengatesh

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

Thomas Dodds

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)

cesar.advincula.o

Hello,

Lets check this example, which so similar as your requirements

1.Criteria section:

2.Formula by each Column:

3. Layout

Kind Regards,

Vengatesh

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.

Vengatesh

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

cesar.advincula.o

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

Vengatesh

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

Thomas Dodds

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:

cesar.advincula.o

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,

Vengatesh

Hi Cesar,

This worked fine.Thanks a lot.

Thanks,

Vengatesh.