To dynamically select value of filter using macros

[#1]

Hi,

I have a requirement to dynamically select first value of every listbox/filter which are present in the dashboard using macros.

Can anyone suggest what would be the approach to be followed? How can we dynamically fetch names of the List Boxes ?

Thanks

[ariel_klien]

Hi,

why are you want to use Macro?

you can use the "FirstSortedValue(Expression)" command .

BR,

Ariel

[#3]

Guarav,

Sounds like bad practice to do this...  Why would you need to do this?  Is it for a dashboard that needs to select the dimensions for a chart?  Perhaps Set analysis would serve you better?

Macros are really not recommended by Qlikview, and specifically advised against in the Best Practices documents, especially if you have Qlikview Server...

Richard

[IAMDV]

Gaurav,

I totally agree with Richard's comments. I guess you wanted to have some default selection hence you're trying to have first value selection in each listbox. It's possible but it will help you if you can explain the actual requirement? Having you tried creating a bookmark and selecting the bookmark. Not ideal solution but just a thought!

Cheers,

DV

www.QlikShare.com

[Colin-Albert]

I would agree with Deepak, use a bookmark.

[nikhilgandhi715]

Hi Gaurav,

It is not recommended to use macros however you can use the below code.

Sub Test_data
ActiveDocument.Fields("FIELD_NAME").Clear
Set vSelVal = ActiveDocument.Fields("FIELD_NAME").GetPossibleValues

For i = 0 to vSelVal.Count-1
Activedocument.Fields("FIELD_NAME").SELECT vSelVal.Item(i).TexT
EXIT FOR
Next
End sub

Also you'll need to sort your listbox by Load Order to get the first value selected using the macro. I am not sure why we have to sort it on Load Order only; but thats the way it works. Do not have much time right now to investigate, I'll check that and shall share the same with you.

Regards,

Nikhil

[#7]

Hi all,

I totally agree with you all that it is a bad practice to use macro but in this case i was trying to automate the test process to test Dashboard. So in order to achieve that i thought of using macros. Anyways, thank you all for your valuable suggestions.

Hi Nikhil,

Thank you for the response. I tried your macro it is working even if List Box is not sorted by Load order.

The macro which i wrote is also serving the purpose. Kindly check and do let me know in case of any concerns.

public function RandomSelection

ActiveDocument.GetApplication.WaitForIdle

set LB=ActiveDocument.GetSheetObject("List Box ID")

LB.Activate

E=LB.GetPossibleValues

i=lbound(E)

ActiveDocument.Fields("Field name").select E(i)

end function