3 Replies Latest reply on Jul 28, 2014 6:41 AM by philmodjunk

    Top 10 of most sold articles / products

    Corné

      Title

      Top 10 of most sold articles / products

      Post

           Hello All,

           I'm trying to make a top 10 list of our most sold articles or products. I've got this table 'invoice lines' which contains all invoiced lines with articles and the amount of sold items. To build a dashboard with some sales info, I would like to create a portal to show the most sold items, later on to be specified per day, week, month... etc. 
           So far a Summarize field with a Count of ArticleID's didn't get me anywhere (obviously).

           Hope you can point me in the right direction to get this done?

           Thanks in advance!
           Corné.

        • 1. Re: Top 10 of most sold articles / products
          philmodjunk

               A summary field can work if you set up a sub summary layout part to show a total for each group of records. You can perform a find to limit the records to those that represent sales for a given time period and then sort to group them by product. You can then use the "reorder groups" option to sort your groups in descending order by your sales count.

               The tricky part is to then omit all but the top 10 groups. A script can loop through your records and when it reaches the 10th group it can use omit multiple records to omit the rest from the report.

               There are also ways that you can list the top 10 in a portal of just 10 rows by using a portal to the products table and a relationship that matches to an occurrence of line items that limits the matching records by a date, a pair of dates or a list of dates in order to limit the count to a given time period.

          • 2. Re: Top 10 of most sold articles / products
            Corné

                 Hi Phil,

                 Thanks for your prompt reply! Given the fact that we like to build a dashboard, I guess I'd prefer the portal option. But as far as I know, I cannot use this in combination with a sub summary, can I? So I need to have another method to calculate a top 10 for most selled products, right? Any suggestions perhaps?

                 Best, Corné.

            • 3. Re: Top 10 of most sold articles / products
              philmodjunk

                   You cannot use a sub summary layout part, but you can set up relationships where this is not necessary.

                   Dashboard-----<Products------<LineItems

                   Dashboard::anyfield X Products::anyfield

                   Products::__pkProductID = Lineitems::_fkProductiD AND
                   Products::gDateStart < Lineitems::salesdate AND
                   Products::gDateEnd > LineItems::Salesdate

                   Your portal would list records from Products with a calculation field defined in products that sums a Qty field in LineItems. You can sort the portal by this sum calculation and limit the number of  portal rows to 10. But note that this requires a sales date field in Lineitems that is an indexed field. You'd need to use an auto-enter field option to copy that value over from Invoices.

                   Alternatively, a single calculation field could list these values by using ExecuteSQL if you have FileMaker 12 or newer: FMP 12 Tip: Summary Recaps (Portal Subtotals)