Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Percentile calculation in WebI

Posted on 2011-09-15
7
Medium Priority
?
3,024 Views
Last Modified: 2013-11-16
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
0
Comment
Question by:mlmcc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 35

Expert Comment

by:James0628
ID: 36553826
 > ... for the selected location ...

 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
0
 
LVL 101

Author Comment

by:mlmcc
ID: 36554151
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
0
 
LVL 35

Expert Comment

by:James0628
ID: 36555211
Sorry.  Wish I could help.  Sounds like it could be a tough one.  Good luck.

 James
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 101

Author Comment

by:mlmcc
ID: 36555258
I think we may have to build it in the universe.

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 36565846
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
0
 
LVL 101

Accepted Solution

by:
mlmcc earned 0 total points
ID: 36567430
I actually figured it out early this morning.

There is a RANK function that returns the rank of the value over the dimension.
To use it I had to add a section to the report on the dimension.

Basic formula is
(Rank(Measure;(Dimension)) / Count(Dimension) IN REPORT)

mlmcc
0
 
LVL 35

Expert Comment

by:James0628
ID: 36568565
Great.  Glad you figured it out.

 James
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

610 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question