Mike McCracken
asked on
Percentile calculation in WebI
I understand what a percentile is and know about the WebI percentile function.
I need to determine the percentile of a value in a given set.
I have a report that shows the average time spent on a project based on the location of the project. The database has values for all locations. The requirement in to show the average time (a measure) for the selected location but they also want the percentile based on the complete set of times for all locations.
How can I calculate that.
I suspect it may be necessary to calculate it as a universe measure but i don't have control over the universe.
mlmcc
I need to determine the percentile of a value in a given set.
I have a report that shows the average time spent on a project based on the location of the project. The database has values for all locations. The requirement in to show the average time (a measure) for the selected location but they also want the percentile based on the complete set of times for all locations.
How can I calculate that.
I suspect it may be necessary to calculate it as a universe measure but i don't have control over the universe.
mlmcc
ASKER
The report as written is filtered to a single location. However I can add a query to the report (and probably will have to) to include all locations for the time frame.
My issue is if this were Crystal, I could use a subreport to get all the locations into an array and count how many the given location was better than and divide that number by the total count.
I am still learning how to do things in WebI and don't fully understand how to loop through the data to calculate what I want.
mlmcc
My issue is if this were Crystal, I could use a subreport to get all the locations into an array and count how many the given location was better than and divide that number by the total count.
I am still learning how to do things in WebI and don't fully understand how to loop through the data to calculate what I want.
mlmcc
Sorry. Wish I could help. Sounds like it could be a tough one. Good luck.
James
James
ASKER
I think we may have to build it in the universe.
mlmcc
mlmcc
That does seem like it might be simplest, if you can do it. Like add a total project time somewhere, so that you can just pull that into the report. Otherwise, it seemed like the report would have to read the records for all locations, to get the total project time, but only show the location that you asked for. That'd be easy enough to do in CR (inefficient, but easy) using suppression, but I don't know about BO. Never had to do anything like that in BO.
James
James
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Great. Glad you figured it out.
James
James
So the report is run for one location at a time?
If it was run for all locations for a project, then I guess you'd just need the average time for a location and the total time for all locations, but if you don't have all locations ...
I don't know how to handle that (my unfamiliarity with WebI is not helping :-). I'm only posting this in case I'm wrong and you do have all locations in the report, in which case there is a slightly greater chance that I could help, but only slightly. :-)
James