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?
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".
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
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.
Thanks, I'll be waiting for your answer. You are helpful as always.
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.
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.
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.)
check you PM. I've shared you a link to a file.