On Sheet Activate> Select Multiple Values in a Fields

[#1]

I attempting to set up a sheet where 'On Sheet Activate' multiple values for a field can be selected in the search string.

For example. Triggers> Select in Field> salesrep_id > Search String> =1022, 3022, etc...

I have tried many combination's of () and keywords in the search string. I have yet to find the correct combination/syntax. Selecting one value works fine but multiple values does not.

The last entry in the below post is exactly what I am after.

http://community.qlik.com/forums/p/16933/66257.aspx

I would also be okay with the data being displayed within a pivot table. If I want an expression to show only: salesrep_id = 3202 AND 1022 AND etc..

The second of the two events would be the most useful. Thanks for any ideas you have to offer.

[prieper]

If you wish to make your selections manually, have the Cntrl-button pressed whilst qlicking or on pressing Enter. This adds current selection to the previously made one. If you wish to create a macro, please have a look into the API-Guide and create a macro on activate:

rem ** cancel selection of first selected value **
set f = ActiveDocument.Fields("Class")
set fv = f.GetSelectedValues
fv.RemoveAt 0
f.SelectValues fv

rem ** select two non-consecutive field values in field **
set f = ActiveDocument.Fields("Class")
set fv = f.GetNoValues 'empty array
fv.Add
fv.Add
fv(0).Text = "Group"
fv(0).IsNumeric = false
fv(1).Text = "Field"
fv(1).IsNumeric = false
f.SelectValues fv

HTH
Peter

[#3]

No I would just simply like to select value A and value B within the same field on sheet active.

If the macro is the only way this can be done I will find a different option like the pivot table. If I could make an expression: '=salesrep_id = 1022 AND 3027' then this would be sufficient but once again I am unsure of the syntax.

Thanks.

[#4]

Are the salesrep_ids you list static? Will you always be selecting the same ones? If so, use Set Analysis on your expressions.

Sum({<salesrep_id = {1022, 3027}>} Sales)

You could use a variable to store the rep ids, like:

Sum({<salesrep_id = {$(vRepIDs)}>} Sales)

If you really want the selections to be made, then I think you need a macro as Peter suggested. I don't think it's possible to put multiple values into the Select Trigger. I'm back on 8.5, so I can't test it out. You could use the ToggleSelect which means keep the current selection and also select. So you could set 1022 to Select and then 3027 to ToggleSelect (use ToggleSelect for any additional).

[#5]

I would like a pivot table to display just sales for salesrep_id equal to 1022, 3027, etc. (We have some split accounts with other salesmen, I would like to display) I do not need to compare it to anything or need to use Set Analysis, this may be further down the road. Currently I just want only data for sales id 1022 and 3027 to show in my straight/pivot table. Yes the sale id are static and I do not need to 'select' them just reduce data based on the values.

Is this the best option? Would data reduction be better to use? I do not want other salesmen to see other salesmen data. Am I taking the wrong approach. I would then make a tab/sheets for each salesmen and use section access to control which tabs they can see and can not see.

Thanks once again.

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

[#6]

I would like a pivot table to display just sales for salesrep_id equal to 1022, 3027, etc. (We have some split accounts with other salesmen, I would like to display) I do not need to compare it to anything or need to use Set Analysis, this may be further down the road. Currently I just want only data for sales id 1022 and 3027 to show in my straight/pivot table. Yes the sale id are static and I do not need to 'select' them just reduce data based on the values.

Is this the best option? Would data reduction be better to use? I do not want other salesmen to see other salesmen data. Am I taking the wrong approach. I would then make a tab/sheets for each salesmen and use section access to control which tabs they can see and can not see.

I just made an expression in my pivot table as 'salesrep_id = (1022) OR salesrep_id = (3202)' this returns a -1 value for the correct sales rep. How do I know only show the values that are equal to -1?

Thanks once again.

Normal 0 false false false EN-US X-NONE X-NONE MicrosoftInternetExplorer4

[#7]

I just made an expression in my pivot table as 'salesrep_id = (1022) OR salesrep_id = (3202)' this returns a -1 value for the correct sales rep. How do I know only show the values that are equal to -1?

[#8]

Use Set Analysis in all of your expressions in the pivot.

Sum({<salesrep_id = {1022, 3027}>} Sales)

Once it is placed in each expression, the pivot will only display values for those two IDs.

[#9 · Accepted solution]

I used data reduction based on salesrep_id. This was the best solution.I made an Excel sheet which stores the ID.

Now I just need to figure out adding AD support for the names...

[#10]

Through it should be noted NMiller solution also worked. Using DDR just worked better for me. Through I will use the section access solution in the future I am sure.

Thanks for your help on this matter.