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).
Original post: How to add a count function to measures
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.
Was this functionality ever rolled out?
Not yet. I hope it is on its way as its becoming difficult without this ability.
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.
Glad to see some traction on this. Please count me in if you want to discuss examples of requirement from myself.
@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.
Yeah sure, please send me an email to arrange.
I’m free both days next week. Feel free to email to arrange a time.
In principle this looks good. My observations:
- 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.
Therefore the above screenshot should be:
Does this make sense?
Riaz, Will be happy to try the iteration!
Correction: Please see below
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.
I am finding it haphazard. Same DB sometimes shows it or not here is the same DB:
Same DB (different Chrome tab):
Sorry no matter what I try the count section does no longer appears on any DB.
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.
Is it too late to sign up to test this?
Hi @Riasat - would I be able to sign up as well? Thanks!
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 hear your frustration @Connie314 Connie
@Riasat - is this under consideration?
Hey @sensor ,
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:
That resulted in the following columns:
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