How to add a count function to measures

howto

#1

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


#2

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).


#3

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?


#4

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.


#5

Thanks Shane. Ill relay this to our programmers.


#6

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


Distinct Count Requirement
#7

+1 on this. A count function would be extremely useful


#8

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.


#9

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.
image
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


#10

I’m sorry, you lost me. What exactly are you trying to measure? The number of invoices in which part “X” appears?


#11

Hi JDogg, correct. Number of invoices where part X is selected.


#12

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.


#13

This is also an issue I have had. Did you ever figure out a workaround?


#14

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


#15

@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


#16

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…