Sort via Macro question

[qlikplaut]

Hello,

I am using a Macro to allow a button to sort a table using the first dimension.

Sub SortChart 

Set ch = ActiveDocument.GetSheetObject("CH01") 

ch.SortBy 0 '' this line specifies the expression to sort by 

Set p = ch.GetProperties 

Set dims = p.Dimensions 

'' Dimensions start by 0 

'' 0 = Disable sort 

'' 1 = Sort ascending 

'' -1 = Sort descending 

i = dims.Item(0).SortCriteria.SortByNumeric 

If i = 1 Then 

  dims.Item(0).SortCriteria.SortByNumeric = 1 

Else 

  dims.Item(0).SortCriteria.SortByNumeric = -1 

End If 

ch.SetProperties p 

End Sub 

When I have 2 dimensions in the table, I would like this macro to sort both of them, first sort using the first dimension and after using the second.

I can do that from Chart Properties-> Sort, but I need to do it over this macro.

Any ideas?

Thank you in advance,

Bogdan

[Gysbert_Wassenaar]

dims.Item(0) is the first dimension. If you want to sort another dimension add another if-then-else block for the second dimension using dims.Item(1). Or add them in the same block if you want to sort all the dimensions in the same order.

[qlikplaut]

Hello,

Thank you for your response. Add another dim. to the macro as you suggested, it will sort the table but the primary sorting option will remain the second dimension. I need to sort primarily from the dim0 and secondarily from dim1. I find it easier to explain with examples:

Here is what I want to achieve:

dim0	dim1
a	1
a	2
a	3
b	1
b	2
b	3

Here is what it does now:

dim0	dim1
a	1
b	1
a	2
b	2
a	3
b	3

Bogdan

[marcus_sommer]

You could change the order in which you applied this sorting or separate them in multiple executions - but perhaps you need only another SortCriteria - see this example from APIGuide.qvw:

rem set load order sort for all fields in chart Mychart

set chart=ActiveDocument.GetSheetObject("CH01")

set Prop = chart.GetProperties

set vars = Prop.Dimensions

for i = 0 to vars.Count-1

    vars.Item(i).SortCriteria.SortByAscii = 0

    vars.Item(i).SortCriteria.SortByLoadOrder = 1

next

chart.SetProperties Prop

- Marcus

[qlikplaut]

Thank you for your reply Marcus.

Might be sloppy, but I managed to make it work, here is what I used:

Sub SortChart

Set ch = ActiveDocument.GetSheetObject("CH01") 

ch.SortBy 1 '' this line specifies the expression to sort by 

Set p = ch.GetProperties 

Set dims = p.Dimensions 

'' Dimensions start by 0 

'' 0 = Disable sort 

'' 1 = Sort ascending 

'' -1 = Sort descending 

i = dims.Item(1).SortCriteria.SortByNumeric 

  If i = 1 Then 

  dims.Item(1).SortCriteria.SortByNumeric = -1 

Else 

  dims.Item(1).SortCriteria.SortByNumeric = 1 

End If 

ch.SetProperties p 

Set ch = ActiveDocument.GetSheetObject("CH01") 

ch.SortBy 0 '' this line specifies the expression to sort by 

Set p = ch.GetProperties 

Set dims = p.Dimensions 

i = dims.Item(0).SortCriteria.SortByNumeric 

If i = 1 Then 

  dims.Item(0).SortCriteria.SortByNumeric = -1 

Else 

  dims.Item(0).SortCriteria.SortByNumeric = 1 

End If 

ch.SetProperties p

End Sub