5 Replies Latest reply: Oct 3, 2011 11:59 AM by jason.pincin RSS

    Selecting First X Values in Expression-Sorted Selection Box

      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

        • Selecting First X Values in Expression-Sorted Selection Box
          Kaushik Solanki

          Hi,

           

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

           

          Regards,

          Kaushik Solanki

          • Selecting First X Values in Expression-Sorted Selection Box
            Liron Baram

            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

            • Selecting First X Values in Expression-Sorted Selection Box

              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...