Pivot Table Query

[#1]

Hello Everyone,

I have certain data like this

Milestone     Order     Value

2123               1          25000

2123               2          30000

2123               3          15000

2220               1          10000

2220               2          40000

I want 'Milestone' and 'Order' both as dimension when I collapse 'Order' I dont want any aggregation funciton to evaluate 'Value' but to pick the highest 'Order' and display it and when I expand 'Order' I want it to diplay all the possible 'Order' and their 'Value' under milestone. Is it possible? If yes, Please help I'll be very thankfull to you.

Best Regards,

Gaurav Tyagi

[sujeetsingh]

It seems that not possible..

[#3]

Hey,

I achieved it by using firstsortedvalue() function. Thanks for your thoughts though.

Best Regards,

Gaurav Tyagi

[#4]

can u post the solution file....

[#5]

Sorry the data is way to confidential in the file.

But its not that tricky how it sounds after reading my query.

Make two dimensions in a pivot table. And in the expression just use this :-

firstsortedvalue([mInvestedEffortstillDate(MM)],-[mMonth(Integer)])

Here, mMonth(integer) Field holds integer values for the months which gives them a rank and I just pick up the highest month row by it.

I'm not able to run any aggregate function inside firstsortedvalue function yet, dont if it is possible or not.

but that query is serving my purpose. Hope it helps you too.

If you need to know anything else please message.

Have a nice day.

Best Regards,

Gaurav Tyagi