10 Replies Latest reply on Jan 7, 2011 4:00 PM by ultranix

    Unique product count (summary)

    ultranix

      Title

      Unique product count (summary)

      Post

      I have a list, where I have subsummaries by product, month and day.

      I am having a problem to separate unique products, that is, I have 46 products and they occur in 118 transactions. I created a summary field, where I put a total of product ID, but it didn't do the trick, it still displayed that 118, because it just counted number of fields having ID without removing multiple occurences of the same product in different transactions.

      I also want this number to update depending if I choose to sort by current month or by date, just like Total SUM of all products does.

      So how do I get the exact number (sum) of unique products to show up properly?

        • 1. Re: Unique product count (summary)
          philmodjunk

          A trick shared by Comment quite some time ago is to "add the reciprocal". It's a bit convoluted, but it works.

          How to count the number of unique occurences in field.

          • 2. Re: Unique product count (summary)
            ultranix

            It doesn't work properly. Because. I have following subsummaries:

            1) Total (which basically shows all records and sorts by 1) subsummary year 2) subsummary month 3) subsummary week 4) transaction date 5) and only then by product ID

            2) By Year (same sort as previous)

            3) By Month (same sort as previous)

            4) By Week (same sort as previous)

            So how can I overcome that?

            • 3. Re: Unique product count (summary)
              philmodjunk

              Do you just want the unique count of each item per week? That's how I read you last post, so let me know if that's not the case.

              The method does work even with records sorted into categories and sub categories.

              Just make sure that the summary field, sUniqueCount, is placed in your sub summary part that is sorted by "week".

              • 4. Re: Unique product count (summary)
                ultranix

                not exactly. It looks like this

                Total                                          Unique products: XX; Total products: XX

                         2011                                 Unique products: XX; Total products: XX

                                  January                    Unique products: XX; Total products: XX

                                                Week 1      Unique products: XX; Total products: XX

                1 Product ID | Purchase date | Notes

                2 Product ID | Purchase date | Notes

                In all 4 summaries (1 grand - Total and 3 subsummaries - Year, Month, Week) I set up the button, so whenever you click on, let's say 2011, it only shows year 2011. When I click on January - It only shows values from January and so on.

                So I want that unique products count to update accordingly, no matter if i click on the year, month or week. I tried to get it working following your example, but as I said, I sort by 1) subsummary year 2) subsummary month 3) subsummary week 4) transaction date 5) and only then by product ID

                • 5. Re: Unique product count (summary)
                  philmodjunk

                  I stand corrected. I took a second look at my demo file where I tested this and see that the category sort followed by the item sort does keep the summary count from returning the correct counts. I'm going to play around with this test file and report back.

                  • 6. Re: Unique product count (summary)
                    ultranix

                    Thanks, I'll be waiting for your answer. You are helpful as always.

                    • 7. Re: Unique product count (summary)
                      philmodjunk

                      The Sum the reciprocals method only works if the items with same product ID are sorted so that they are grouped adjacent to each other. Including transaction date, prevents that.

                      There is a way to compute these counts that counts the values in a conditional (relationship based) value list that can be used, though it requires defining a self join for each sort level where you want a count of unique items.

                      ValueCount ( ValueListItems ( Get ( FileName ) ; "yourValueListNameInQuotes" ) )

                      Define a self join that matches records by Year. Name the new table occurrence "TransactionsByYear". (In Manage | Database | Relationships, click your original table, then click the button with two plus signs.)

                      OriginalTable::Year = TransactionsByYear::Year

                      Define a value list, Same Year IDs,  that lists values from the Product ID field of TransactionsByYear, use only related values starting from OriginalTable.

                      Then ValueCount ( ValueListItems ( Get ( FileName ) ; "Same Year IDs" ) ) will give you the unique count of Product IDs for all transactions of the same year.

                      • 8. Re: Unique product count (summary)
                        ultranix

                        Yes, it returns of the values of all unique products, but when it's not updating accordingly when i click on particular year/month/week.

                        • 9. Re: Unique product count (summary)
                          philmodjunk

                          What do you mean by "click on year/month/week"? Can you describe that in more detail?

                          Is the calculation field set up as an unstored calculation? This only updates correctly if it's a field of type calculation with "do not store" specified in storage options. (a number field with an auto-entered calculation won't work for this either.)

                          • 10. Re: Unique product count (summary)
                            ultranix

                            check you PM. I've shared you a link to a file.