topic Sort individual field in straight table by descending order using macro in QlikView

Sort individual field in straight table by descending order using macro

udaya_kumar — Wed, 11 Apr 2012 10:21:18 GMT

Hi,

i want to sort individual field separately as descending order by using macros (VBScript)

how i can do that?

Re: Sort individual field in straight table by descending order using macro

Anonymous — Wed, 11 Apr 2012 10:23:13 GMT

Please see the attached file and you can change the qv file based on your requirement

Hope this will helpful for your problem

Regards

Ashok

Sort individual field in straight table by descending order using macro

Miguel_Angel_Baeyens — Wed, 11 Apr 2012 10:26:51 GMT

Hi,

Check this thread and the file attached to see a working example on a macro to sort in a chart.

Hope that helps.

Miguel

Sort individual field in straight table by descending order using macro

udaya_kumar — Wed, 11 Apr 2012 10:36:24 GMT

Hi Miguel and chand,

thanks for the reply,

i need to sort the expression field by descending order in the straight table, not the dimensions.

Re: Sort individual field in straight table by descending order using macro

Miguel_Angel_Baeyens — Wed, 11 Apr 2012 11:00:53 GMT

Hi,

There may be some misunderstanding here. Expressions are results, not values, and cannot be sorted. What you do can do is sort a dimension using an expression, so, for example, in a chart that displays all your customers and their corresponding current year sales, you can sort the dimension Customer by the Sum(Amount) of sales ascending or descending, although you double click on the expression to sort.

Add the following code:

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(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

1 will refer to the first expression, 2 to the second and so on.

Hope that helps.

Miguel

Re: Sort individual field in straight table by descending order using macro

Wed, 11 Apr 2012 12:06:58 GMT

Hi Miguel and Chand,

Dimension is getting sorted based on the expression value. But it is toggling between ascending order and descending order. I want to set it to descending order only. Can it be done ??

I m using this macro

Sub SortChart
Set ch = ActiveDocument.GetSheetObject("CH05")
ch.SortBy 1
Set ch = ActiveDocument.GetSheetObject("CH15")
ch.SortBy 1
Set p = ch.GetProperties
Set dims = p.Dimensions
i = dims.Item(0).SortCriteria.SortByNumeric= -1
ch.SetProperties p
End Sub

Re: Sort individual field in straight table by descending order using macro

Miguel_Angel_Baeyens — Wed, 11 Apr 2012 13:18:55 GMT

Hi,

You are using two charts to sort by but the dimension is only in one of them. Something seems to be wrong there, or at least make little sense, since the "dims" variable stores the dimension of the CH15 chart, and not the CH05. Maybe is that driving the macro interpreter crazy?

Regards.

Miguel