Hi again,
Sorry for stealing @KPICKLES original post. But thought it was prudent to add this to the product suggestions board.
At the moment there does not appear to be a means of performing distinct counts on dimensional values. Examples of these are total number of customers or number of orders based on what the user has focused on. These requirements are pretty common questions to ask of your data.
I see we have a “Market” mode which does perform a count based on a dimension you select, but is limited to that mode and it serves a specific purpose. This means to get a report the way you want, you need to use “Custom” mode and then loose this count ability.
So I wish to suggest that either at the database design level or in “Custom” mode we have an ability to perform different aggregations based on dimensional value. I believe this would have to be at “Custom” mode due the dynamic nature of the selections.
If there is any clever workarounds for this I would welcome them. All attempts so far have not worked 100% due to user filtering on the database. (i.e. set up a flag on first record of order line, then you can sum it, but if you filter for specific line item you loose the count).
Thanks again for your suggestion Jon. We discussed a similar issue just this week, and agree that it is something worth considering for a future product release.
I just asked support this EXACT questions. I need to know the # of orders and the # of invoices generated. There was an option for “order count” already in my database for P21 and its actually counting the first property, in our case the item id. UGH caused some bad reports.
@Riasat I’d be more than happy to discuss as well. I’ve always found this to be one of the biggest missing pieces in Phocas. We’ve used a number of different work arounds to get by, but would like to see an easier way of doing counts.
The count menu item, I think the measure types (sum, count) should be seperated to make the menu less cluttered. I.e.:
This is count, what about count distinct?
I.e. number of ship tos at customer level could be 20, but thats 20 invoices, in actual fact there are only 5 ship to locations, so rather than seeing 20, we would want to see 5.
Another example, take number of parts customer has ordered, a count would show say 200 (invoice lines), but its actually only 7 parts they have purchased across the date range.
I noticed that this count feature is only available on NEW databases, dont appear to have this feature on pre-existing DB’s. May have missed reading that somewhere.
Will this feature be enabled on “historic” DBs when made permanent?
Now I have a new DB I will perform some analysis and get back to you next week.
Correction.
I am finding it haphazard. Same DB sometimes shows it or not here is the same DB:
Riassat,
Would it be possible for me to be added to test this? Counts have been a big sticking point for us and it looks like you are on the right track.
Thanks,
Ted
Hi Riasat,
I have used the Count/Customer measure, but it is frustrating that I can then NOT use that field/column in any calculations I want to add. Example, I’m trying to get an average sales amount by customer (value / count(customer)) for the period I have selected. But the field/column is not available to select in Custom Mode/Measure.
if anyone else has figured out how to do this in different way, I would LOVE to hear about it. Since our customers routinely have more than one invoice in a given day, let alone period (due to keeping their accounting by departments clean), it’s been frustrating to get the numbers that our Sales Manager is looking for on a dashboard.
I have a similar if not the same frustration. I have created a couple of summary widgets to count # of customers in one period versus another to track distribution. Ideally, in Custom Mode, I would like to create calculations to show # of customers ín the çurrent period less # of customers in the previous period aka +/- Growth and another to show the Growth Percentage based on those two counts - but count is not a measure in the list of measures I can use in the calculation.
If this could be addressed - we would finally have a useful visual representation of Distribution.
I have somewhat of a workaround, but it is certainly not an ideal solution. In my case, I recently built my own “Branch Count” Measure that I could use in Custom Mode calculations. However, I imagine you could repeat this with a customer list for a “Customer Count” Measure.
Basically, I pulled my “branchdata” file into the Transaction area of Database Design. Then, I created 2 Transform columns in that file:
Expression to pull “Today” as a date:
If-Then to exclude our administrative branch (99) from the count. You could modify this if-then to suit your needs:
As you can see, I mapped the date of “Today” to the Date, and the Branch Count column to a Measure called “Branch Count”.
Then, on the front end of the database, I can pull that in Custom Mode by pointing it to that Measure with the Period of “Today”.
My only caveat is that this function has been fluctuating by working with Periods of “Today” on some days or “Yesterday” on other days… which has apparently thrown Support and their team for a loop. However it SHOULD work properly (in theory) when using a period of “Today.”
The only other thought I have is that you might be able to pull a customer count in the Transaction data itself by adding an Expression through a transform column. Phocas set this up for “Order Count” which counts an order only once, regardless of the number of lines it shows up on. I imagine the same could be set up on the CustomerID, but this is the SQL on our Order Count:
case when row_number() over (partition by Ord_ID order by Ord_ID) = 1 then 1 else 0 end
