Hi PUG Network,
We need to add a count function to measures for our orders data base. Currently it looks across the order lines and sums the value of those rather than the individual order headers. Is there an easy way to add a count function without having to write code or a new database?
Regards,
Kieran
So I understand this, you want to show the number of orders you have? We have standard measures in our sales database for Number of Invoices and Number of Invoice Lines, when used in the orders stream this shows number of orders and number of order lines as a measure.
Alternatively you can take the the data to transaction level and make a gauge using the count function instead of the sum function for a widget that shows the number of transactions (orders).
Hi Shane, this is correct. We want to be able to plot the volume of orders coming through each week/month so we can visualise the trend. I have used the transaction line function with a count on the gauges however it doesnāt not work for the headers. So, how have you programmed or setup the measures for number orders and number order lines?
Our Phocas partner SlideRule set them up as measures for us, unsure of the technical side of it, but I know they extract the data by both lines and headers and this allows them to do it for us.
Thanks Shane. Ill relay this to our programmers.
This is something that we also need. We are new to Phocas so donāt have everything set up yet. I notice some databases have COUNT as a measure and some do not.
We need the ability to count any number of rows using typical ācount distinctā logic similar to SQL or even Excel. Should not need programmers to create such a measure. It is standard on the fly in Power BI and even Excel. I hope that there is a tutorial on this topic, but if not we will post a solution when we get it solved.
Steve Shuman
Nelson Electric Supply
262-504-3617
+1 on this. A count function would be extremely useful
I managed to find a clever way to do this using focus design database sync.
From the DB designer you simply add an expression column to your DB/Stream. The formula is ā=1ā. This adds a new column with the number 1.
Then make that column a measure. From there you can total and get your count of limes in a DB.
You can even get clever-er and create an if-then statement by keying off other columns with in the database. For instance, in our system all invoices begin with āIVā in the document number so I create an it-then statement stating āif keycode = āIV%ā then 1 else 0.
I add this column as a measure and I have a count of all invoices.
Hope this helps.
Hi @JDogg016 ,
I have recently too had the same issue and yes you are correct, I have used this to some success. I have found this will work at header level.
However, it isnāt dynamic, the problem I have found is this is at the data level and the expressions are calculated during the building stage. An example of a dynamic count would be as follows:
If for example you are working at invoice line level (which we do) there could be 10 lines on the one invoice, how do you count the number of invoices when you filter by a specific part? For example, using āIs First Line?ā as our count of invoices(If statement created in the designer), when I filter on invoices where part A is being used I would get the answer 1, when it should be 2.
Part A is on IV00001 on line 1 so is counted as invoice, however, Part A in IV00002 is the second line and would not be counted.
I donāt currently see a way around it (unless I am missing something) without adding considerable more data to Phocas to derive a simple question that count distinct on the invoice number would solve. I would suggest that this is a calculated measure in the designer so it would be dynamic.
Thanks
Iām sorry, you lost me. What exactly are you trying to measure? The number of invoices in which part āXā appears?
Hi JDogg, correct. Number of invoices where part X is selected.
So I had to think about this one and it is not the prettiest result but I actually made Invoices a Dimension (yuck).
I would then be able to go into my product dimension, select a part, then select the invoice dimension and grab a count.
I could also, should I choose, create a custom column with the same information.
This is also an issue I have had. Did you ever figure out a workaround?
Hi @ndeccio
I am afraid not to a satisfactory level. @JDogg016 suggested (see Jun 5 post in thread) creating another dimension for invoice numbers, I could then sum the numbers. The problem was my counts were on order numbers,invoice numbers, Product/Part,customers. There were so many dimensions to add it made build time go from 45 mins to 120 minutes.
I also considered creating a custom SQL data set which just the counts with all other dimension attributes to link to. The problem is our users will come up with weird and wonderful analysis requirements regarding counting and I cannot factor in all of them, so I would be constantly updating this table. (i.e extra data set which has each Order,Invoice,customer,product/part dimension value and a count next to them. This can be Summed as a measure). Not to mention, as soon as you add time to the mix those counts in the data set would become problematic.
I consider this probably my #1 issue with Phocas, dynamic distinct counts would solve so many headaches on the data.
Counts do exist in Phocas if you look at chart gauges, but this is not enough as I need them in a grid.
At the moment to reduce dev time, I am telling the business that the easiest way to do this is to export a list to Excel and then do the count analysis there. This is not ideal, and really hope this option becomes available soon as the distinct counting of orders, invoices, customers is pretty basic and has been available in all other BI solutions for many years. Donāt get me wrong I think Phocas is one of the best BI platforms around, this though is a big problem for the analysis we are doing.
Sorry I couldnāt give you a solution.
Regards,
Jon
@JonKemp ,
Thanks for the response. I had considered all of your suggestions and agree they are to much of headache to try and manage. I am currently having them do excel work as well, but not everyone is capable or willing to do this. I definitely agree that this needs to be addressed ASAP.
And yes, we moved to Phocas over the last few months and have been blown away with all of the capabilities, so I am hoping that this is in the pipeline to be released and/or developed in the near future.
Thanks,
Nick
We have had an open case with Phocas for a while on this. Ultimately it is not possible to dynamically calculate because we canāt use DISTINCT COUNT in the code for measures (as Jon mentioned earlier). Until they add this functionality weāre kinda solā¦
My company could also use the functionality. We would use the distinct count for Open Sales Orders (how many sales orders are past due; how many Open Sales Orders are to ship this month). Another application is on the Purchase Order side of the house. (how many purchase orders are past due; how many purchase orders are to be received this month).
How does this get pushed up on the request list for Development
Bill Gehr
The Dupps Company
Hi All,
Just thought I would let you know how I got a workaround to the issue. This may not work for all of you but thought I should let you know.
In my case I needed a distinct count of customers first ordered, but we wanted it dynamic based on the products as well. For example, questions sales wanted to ask were:
The current sales database we had would not support this (too granular) and the only way to achieve would be with distinct counts dynamically on the customer and product dimensions (something we cannot do in Phocas).
The workaround I came up with was to create a copy of the sales database but change the fact feed to NOT have any specific sales data but instead have a new script which performed the counts on the dimensions we needed.
Since the database was technically the same structure (dimension-ally speaking), it allowed us on a dashboard to have both the sales database and new sales count database on at the same time. This meant, if the user selected the customer, the dimensions were shared and then filtered both the sales and sales count databases at the same time.
Its not ideal, and increases the admin overhead (new dimension in sales = edit sales count db at well) but allowed us to proceed on our trending we needed to perform.
Hope this helps anyone who desperately needs distinct counts.
Regards,
Jon
Hi All,
Iāve read through this topic and a lot of it is way over my head. So Iāll explain what Iād like to do and would appreciate any feedback or responses on if it can be done.
Iād like to know how many times each customer have purchased from us current month to date. The way Iām thinking is that this would be a count of unique Sales Order Numbers by customer (which we can see in Transaction mode). Initially we want to identify which customers have only purchased once so far month to date.
Let me know if any questions around this, otherwise thanks in advance.
Daryl
Hi Daryl,
What I would do is to include a calculation field that apply the logic that if line number = minimum line number of the Sales Order, then = 1 else = 0.
In that sense, Phocas will Sum the 1 for each Sales Order.
Example: Sales Order No #10 have 10 lines of details. The calculated field will have 1 on line number 1 and then 0 for the rest.
You can do this in 2 ways:
Option 1 - Do it in you source data extraction, with 1 of the colum that apply the logic ; or
Option 2 - include Line number as a column, include Min Line Number as another column. In Phocas Designer, create Transform Column that if(Line number = Min Line Number,1,0)
I think Option 1 will be more efficient as it is done in the source data.
Note: I have considered the scenario where some system allow delete of Line Number and thus minimum Number may not be 1.
Hope that help.