I currently have a macro that displays the selected values of a field in a listbox.
set s = ActiveDocument.Fields("DrillField").GetSelectedValues
for i = 0 to s.Count-1 msgbox(s.Item(i).Text) next
However, no matter the order I select the values in the list box, the array of selected values always comes back the same as the listbox's sort order. Is there a way to retrieve the selected values of the field in the order that they were selected?
What I'm trying to do is the following: I want to be able to dynamically drill a pivot by adding and removing dimensions. But I need a way for the user to choose the dimension to breakdown the pivot by and the order of the dimensions. To that end, I have a data island that lists the dimensions to drill into, the field name of the dimension and the drill order.
I have the macro able to trigger when an option in the listbox is checked (using Windows checkmark box style). The macro sets all unselected dimensions' drill order to 0 and then attempts to add 1 to the drill order of all selected fields. The theory is that a selected field increments in the order of its selection. So, the first field to be selected will always have the highest Drill Order. The last, the lowest.
sub DrillTo ' Set Drill Order of unselected fields to 0 set p = ActiveDocument.Fields("DrillField").GetAlternativeValues for i = 0 to p.Count-1 set Result = ActiveDocument.DynamicUpdateCommand("UPDATE * SET DrillOrder = 0 WHERE DrillField='"&p.Item(i).Text&"'") next
' Increase Drill order for all selected fields set s = ActiveDocument.Fields("DrillField").GetSelectedValues for i = 0 to s.Count-1 MsgBox(s.Item(i).Text) set Result = ActiveDocument.DynamicUpdateCommand("UPDATE * SET DrillOrder = DrillOrder + 1 WHERE DrillField='"&s.Item(i).Text&"'") next
' Get Selected values in Drill Order ' Add to Pivot using AddDimension() end sub
This really isn't working though. The second DynamicUpdate doesn't seem to work becaue the listbox which is ordered by DrillOrder and is displaying the value of the field, does not change. Can anyone tell me if there is a better way to do this?
After a lot of banging my head against a wall, my macro is working. I have Qlikview dynamically drilling in a pivot so that the end user just needs to check and/or uncheck any dimensions in any order and the pivot will update to breakdown the numbers in the same order.
I took the Film Example document and added my Drill table and macro to it. Hopefully, it works for everyone. There are lots of extras that could be added to it such as automatically adjusting the column width when a dimension is added or any other Dimension property that exists for that matter. I would just add a column in the DrillPaths table to store that information and then just adjust the macro to apply it when loaded.
Of course, this currently only works on a single Pivot table but it could be expanded to work on any pivot table and any set of dimensions. If anyone has any suggestions, on how to do this better, please let me know. This was my first macro which was made for my first full project in Qlikview so go easy on me.