Our users are requesting an average for the ‘Overall Efficiency’ column on a dashboard. Efficiency is displayed as a percentage (format set in BAQ), but the format is not carried through to the summary. I prefer not to tell users that it’s a ‘known issue’. So, is there a way to make this happen?
Your easiest way may be to change your Column Title to Overall Efficiency % and then drop the % sign from the column values. This way export to Excel will make Excel happy as well.
The more complex option is you Customize and modify the Summaries Display Format, not sure to what but to something.
You shouldn’t average a percent either… It doesn’t really mean anything.
If you really want that, the best way to do that is to make windowing functions to sum up the total expected, and the total actual, and do a percentage of the overall.
Sum(job.estimate) over (partition by table.field)
Sum(job.actual) over (partition by table.field)
If you want the whole table, just do something like company where it’s all the same.
Then divide those to get your percentage. They will be the same for all rows.
In your dashbaord, hide those 3 field, and in the tracker, show the % field, and don’t call for a prompt. It will show the overall percentage for you.
I suspected that the average of percentages wasn’t statistically valid, so I initially omitted summary on that column. The business wants to see some kind of aggregate, which is understandable.
I’m not familiar with PARTITION BY but it sounds interesting. Within a BAQ, is that something I would do in a calculated field? Glancing through the Query Builder, I’m not seeing that function anywhere.
Yes, you would type that in the calculated field. You can use almost any SQL function in a calculated field and it will take it. Search the site for windowing functions and you will find some examples. If you get stuck, you can show me what you have and I can help.
Here’s a site that explains how they work.
and here’s a good example of a windowing function in action. It’s using ROW_NUMBER() instead of SUM() but it’s the same basic concept.