topic Re: Macro to Sort Listbox dimension and expression in QlikView

Macro to Sort Listbox dimension and expression

VivenReddy — Thu, 11 Feb 2021 18:40:36 GMT

I need assistance in creating macros that will sort a listbox. One macro needs to sort the dimension (asc) and one macro should sort the expression (desc). I need to be able to switch between the 2 sorts using buttons.

A sample qvw has been attached.

Re: Macro to Sort Listbox dimension and expression

Frank_Hartmann — Thu, 11 Feb 2021 20:03:52 GMT

attached sample might be helpful although its not using any macro.

hope this helps

Re: Macro to Sort Listbox dimension and expression

QFabian — Thu, 11 Feb 2021 20:38:27 GMT

Hi @VivenReddy please try this macro, i used 'LB01' as Id, becasue is the id from your example qvw

Dont forget to add a 'external\ run macro' action, using the macro name

Let my know if it works

sub sort
set LB = ActiveDocument.GetSheetObject("LB01")
set box=LB.GetProperties
if box.SortCriteria.SortByState = true then
box.SortCriteria.SortByState = false
else
box.SortCriteria.SortByState = true
end if
LB.SetProperties box
end sub

Re: Macro to Sort Listbox dimension and expression

QFabian — Thu, 11 Feb 2021 20:47:10 GMT

Dear @VivenReddy , can you just create a straight table with youProduct as dimension, and one expression, with the same expression that you use in the list box?

With that, you just to double click over the Sales expression title to change the order of Products and use the Product colum to filter.

You can even add aselector for the Product column

Re: Macro to Sort Listbox dimension and expression

VivenReddy — Wed, 17 Feb 2021 00:51:04 GMT

Thank you @Frank_Hartmann and @QFabian for your great suggestions. Unfortunately the example I posted did not match my use case. I've attached another example. See screenshot below. My listbox actually has 2 expressions and I need buttons to sort the expr 1 in ascending order and expr 2 in descending order. Is this possible?

Re: Macro to Sort Listbox dimension and expression

QFabian — Wed, 17 Feb 2021 02:10:17 GMT

Hi @VivenReddy , please check your updated qvw here attached, it has two macros associated in the buttons.

Here the little macros, that can be found in APIGuide.qvw:

sub order1

set fld=ActiveDocument.GetField("Product")
set fldsrt = fld.GetDefaultSortCriteria
fldsrt.SortByExpression = 1
fldsrt.Expression.v = "only({<Product>}Description)"
fld.SetDefaultSortCriteria fldsrt

end sub

sub order2

set fld=ActiveDocument.GetField("Product")
set fldsrt = fld.GetDefaultSortCriteria
fldsrt.SortByExpression = 1
fldsrt.Expression.v = "sum({<Product>}Sales)"
fld.SetDefaultSortCriteria fldsrt

end sub

let me know if this is an acceptable solution

Re: Macro to Sort Listbox dimension and expression

VivenReddy — Wed, 17 Feb 2021 16:40:06 GMT

Thanks @QFabian. I did see this in the APIGuide however I didnt think it would work for me for the following reasons:

1. The macro does not reference an object - so I was not sure how it would affect that field in the rest of my app.

2. What if the field in my listbox is an expression?

I also noticed that your macro to sort the description is not working correctly. Is there an issue somewhere?

Re: Macro to Sort Listbox dimension and expression

Frank_Hartmann — Fri, 19 Feb 2021 14:20:55 GMT

see attached file

Re: Macro to Sort Listbox dimension and expression

VivenReddy — Fri, 19 Feb 2021 16:41:50 GMT

Thanks Frank - I did eventually implement your solution in my app.

If there is no other solution by next week I'll mark this as the solution.