topic Re: Pivot Table Query in QlikView

Pivot Table Query

Mon, 14 Jan 2013 05:52:10 GMT

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

Re: Pivot Table Query

sujeetsingh — Mon, 14 Jan 2013 06:33:56 GMT

It seems that not possible..

Re: Pivot Table Query

Mon, 14 Jan 2013 09:30:48 GMT

Hey,

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

Best Regards,

Gaurav Tyagi

Re: Pivot Table Query

Mon, 14 Jan 2013 09:34:24 GMT

can u post the solution file....

Re: Pivot Table Query

Mon, 14 Jan 2013 11:45:00 GMT

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