topic Dynamic sorting on pivot Table in QlikView

Dynamic sorting on pivot Table

Fri, 19 Nov 2010 14:47:32 GMT

HI,

Please help me in Dynamic sorting on pivot Table like stright table ,where ever we clik on table cols, data has to sort.

thanks in advance.

srini

Dynamic sorting on pivot Table

Anonymous — Fri, 19 Nov 2010 15:26:48 GMT

Calculated dimensions can be sorted only by alfabetical order or loading order.

If you want something more complex you can try to set up a macro that runs your ordering algorithm and try to write the value in the cells, but I don't know if it works...

Dynamic sorting on pivot Table

srinivasa1 — Mon, 22 Nov 2010 07:11:52 GMT

Hi ,

Thx for reply,

hey can u please guide me to how can achive this writing macro? do you have any example...

thanks once agian.

srini

Dynamic sorting on pivot Table

Anonymous — Mon, 22 Nov 2010 08:02:30 GMT

If you don't want sort a calculated dimension you should check the pivot table box API. I think that in the property class there is something to set up the sort order (usually the API realize by functions what we can see on the object property window).

I checked the API, I didn't find something specific for Pivot table...what you can try is to modify the default sort criteria for a specific field...I don't know if it works, but you can make a try:

set fld=ActiveDocument.GetField("Salesman")
set fldsrt = fld.GetDefaultSortCriteria
fldsrt.SortByExpression = 1
fldsrt.Expression.v = "sum(Amount)"
fld.SetDefaultSortCriteria fldsrt

If you must sort a calculated dimension your target is harder to reach..I think the only solution is to calculate the dimension in the script pahse (so it isn't anymore a calculated dimension).

If the macro doesn't work you also try to build in the script a set of preordered dimensions and by macro each time you press a button you rebuild the table with the appropriate dimension. (you also build serveral table, each one preordered by a specific criteria and by a macro set the "visible" property, so you can always display the correct ordered table; the drawback is that you have to maintain several table: one for each sort criteria).

I hope these tips can help you

regards,

Matteo