Selecting First X Values in Expression-Sorted Selection Box

[#1]

I'm trying to create a button that selects the top 12 items (by sales). What I'm working with is a selection box that is sorted by an expression so that the top 12 items appear first (and the rest after that in descending order from top sales to no sales).

I've created the following function in VB; similar to a function I have working to select the last 12 months in the calendar selection box, the big difference being the calendar selection box does not use an expression-sort:

Sub t12Selector

          set fld = ActiveDocument.Fields("rMasterItemNo")

          set masterItems = fld.GetPossibleValues

          for i = 1 to 12

                    fld.ToggleSelect masterItems.Item(masterItems.count - i).Text

          next

End Sub

When I execute the above function via a button however, the top 12 items do not get selected. The selection instead seems almost random. For reference, here is the sort expression from the selection box in question (I'm actually sorting on one of 7 sales values depending on another lag selection box):

=Sum( {$<rMasterItemNo=>}

           if (rReturnLagOffset = '6', rSaleAmountLag6

          ,if (rReturnLagOffset = '5', rSaleAmountLag5 

          ,if (rReturnLagOffset = '4', rSaleAmountLag4

          ,if (rReturnLagOffset = '3', rSaleAmountLag3

          ,if (rReturnLagOffset = '2', rSaleAmountLag2

          ,if (rReturnLagOffset = '1', rSaleAmountLag1

          ,if (rReturnLagOffset = '0', rSaleAmountLag0

          )))))))

)

I'm assuming that GetPossibleValues is not returning a list of possible values in the same order that my expression sort is. What I want, is the actual top 12 items in that selection box to become selected. Does anyone know of any work-arounds, or of something I may be missing?

Suggestions/thoughts much appreciated!

Jason

[kaushiknsolanki]

Hi,

   Can you please describe what are you trying to achieve. What output you expect.

Regards,

Kaushik Solanki

[lironbaram]

hei

i would create a list box

with only the top 12 items

by using something like:

aggr(if(rank(Sum( {$<rMasterItemNo=>}

if (rReturnLagOffset = '6', rSaleAmountLag6

,if (rReturnLagOffset = '5', rSaleAmountLag5

,if (rReturnLagOffset = '4', rSaleAmountLag4

,if (rReturnLagOffset = '3', rSaleAmountLag3

,if (rReturnLagOffset = '2', rSaleAmountLag2

,if (rReturnLagOffset = '1', rSaleAmountLag1

,if (rReturnLagOffset = '0', rSaleAmountLag0

)))))))

))<=12,rMasterItemNo),rMasterItemNo)

then via macro select possible in the listbox

if you can post sample data

we can work it together

[#4]

Kaushik,

I have several line charts on a tab, one for return rates (returns divided by sales as percentages) by item, one for return rates by customer, and 3 straight tables that provide tabular totals for sales and returns. By default we want to see this data for the last 12 months for the top 12 products. I created the ltmSelector function for the last 12 months easily which I can fire via a button or on document load. I'm having difficulty with the top 12 items though. These same charts need to honor all selections so that they can change the date range and items/categories/etc. This is just a common view they want to default to or be able to quickly return to.

Liron,

That's a good idea and one I hadn't considered, but can I have multiple list boxes for the same field? Currently, I'm getting possible values with ActiveDocument.Fields("rMasterItemNo").GetPossibleValues. How would it know, or how would I tell it to use the top 12 list box versus the list box containing all items? Perhaps when you say Macro you're referring to something other than VB Script? I'll need to look into that...

[lironbaram · Accepted solution]

after building the listbox

use this vb function

Sub t12Selector

set lb = ActiveDocument.GetSheetObject("LB01") - enter your list box ID
lb.SelectPossible

End Sub

[#6]

Thanks Iiron - your suggestions worked like a charm.