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.
thanks!
Julie
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.
Yes you are correct I want the percentage. I have another BAQ where I am trying to get the average. It’s a similar problem that I can’t figure out.
This is what I’m tying to get:
Month/year configured non-configured total month%
May 2018 350 1124 1474 23.74
thanks!
My table did not line up and I can’t seem to insert a picture of my table. Hopefully you get the idea.
First, a little housekeeping. 
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.
Interesting, you can paste in the HTML tables with the HTML Tables generator – TablesGenerator.com and it creates a pretty table.
| name | Count | Price | ||
|---|---|---|---|---|
| Tim | 15 | 10 | ||
| Alice | 25 | 9 | ||
| Fred | 300 | 8 |
At a minimum for formatting, use three 'ticks and the word text, like:
To get
Month/year configured non-configured total month%
May 2018 350 1124 1474 23.74
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.
Perhaps you just need another Wrapper or you have something off in your calculated fields.
0.WrapperTopLevelSubQuery (This is where you do your Final Calculations)
It would help if you posted screenshots or the baq
I figured it out. I had to multiply the numerator by 100 and then divide.
I’m still wondering why I can’t paste my Greenshots.
You have to click the copy Icon on the greenshot toolbar. I know the Edit menu says Ctrl+C is for copy, but it doesn’t
Turns out the Copy, Cut and Paste functions under the Edit menu are for doing those to objects added to the snapshot (like boxes, arrows, etc…)
Do you have the field type set to integer? It should probably be decimal.
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:
convert(decimal,ConfiguredJobs.Calculated_ConfigJobCount) / convert(decimal,AllJobsTotal.Calculated_AllJobsCount) * 100
Now I have a percentage that is not rounded.
