I have two different types of jobs that are based on the part number description (not job part description): configured and non-configured. I’m trying to get the average number of configured jobs by month. I can count the configured jobs and I can count all jobs. However I can’t figure out how to get the average of those two counts (configured count divided by all jobs count). Is this possible in a BAQ? I want to show the average by month and year.
Yes, it’s possible, if what you are asking for makes sense…
(configured count / all jobs) isn’t an average, it’s a percentage. Do you want and average of the monthly percentage? Or the overall yearly percentage?
These would be similar, but not the same.
Look up windowing functions to be able to get the groupings you want without actually grouping things. (I’m assuming that’s what you are trying to do)
Clearly there is a lot of assumption going on, so another helpful thing would be for you to mock up a table of what you want the data to look like when you are done. That will help people help you get what you want.
Check out this tool for a great way to get screen shots. Then you can just copy paste them right into the thread. You’ll find you’ll use this tool everywhere.
And this link will help you make a table. If you don’t want to a screen shot.
So to answer your question. You need to be able to group things. So the first step (I’m assuming) is you need to be able qualify whether it’s configured our not, correct? Do you have that part? That’s going to be a calculated field. What do you have so far on that front?
I use Greenshot.Com as my screen clipper of choice… it is free and super easy to use. Here is a screenshot of GreenShot that I posted earlier today (or yesterday?) I have greenshot setup to auto pop the selection window on Ctrl-Shift-P, and then it pops this window when done selecting so I can make notes.
I use Greenshot. I don’t have the option to paste the copied screen shot into this post for some reason.
Anway I have 3 subquries to get the total number of jobs, the number of configured jobs and number of non-configured jobs. In the toplevel query I’m trying to divide the number of configured jobs by the total number of jobs. I get zero.
I was trying to get the average of two counters. I had to make the count variables integers. Intuitively I thought you could divide 2 integers. But not in the C# world. I ended up further refining the code to: