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.