MDX Query percentile 25th, 50th and 75th -


i have question , haven't been able find answer (neither in forum nor other) looking for:

  • i need calculate 25th percentile, median (the 50th percentile) , 75th percentile.

putting in words: need write in mdx query in ssrs tell me data 25th, median , 75th

all able find far not exact values of each 1 of them

thanks

i've been working on same issue own data. trouble having in figuring out median() function. here's how interpret parameters of function:

microsoft's definition: median(set_expression [, numeric_expression])

my interpretation: set_expression set of values define grain measure summed before median evaluated

numeric_expression measure summed, set of sums sorted , evaluated find median

in case finding straight median across entire data set, didn't want sum values @ all. prevent sums being calculated, used key attribute dimension had 1-1 cardinality records in fact table contains measure i'm using. flaw i've seen far median returns whole number when there number of records , mean of 2 middle records should result in number ending in .5. example, values of 2 middle records 16 , 17 , function returning 17 instead of 16.5. since minor flaw, i'm willing overlook now.

this calculation median function looks like:

with member measures.[set median] median(     [dimension].[key attribute].members     ,measures.[non-summable measure] )

i used combination of median , topcount 75th percentile. use topcount limit set median second half of data since topcount sorts data in descending order. i'll explain how understand topcount:

microsoft's definition: topcount(set_expression, count [, numeric_expression])

my interpretation: set_expression set of values desired number of tuples returned

count number of tuples return set

numeric_expression value used sort set in descending order

i want median function use last half of records in fact table returned in query, again use key dimension table has 1-1 cardinality fact table , sort measure want find median value.

here how coded member:

member measures.[75th percentile] median(     topcount(         [dimension].[key attribute].members         ,measures.[fact table record count] / 2         ,measures.[non-summable measure]     )     ,measures.[non-summable measure] )

so far, combination of functions has returned true 75th percentile data set. 25th percentile, tried replacing topcount in code bottomcount, supposed same thing, sorting data in ascending order use first half of records instead of second half. unfortunately, haven't been able null combination of functions, i'm open suggestions on how 25th percentile.

this how final query looks:

select     {         measures.[set median]         ,measures.[25th percentile]         ,measures.[75th percentile]     } on 0     ,[dimensional row members here] on 1 [cube]     [non-axis dimensional filter members here]

Comments

Post a Comment

Popular posts from this blog

Why does Ruby on Rails generate add a blank line to the end of a file? -

running rails generate controller foo home results in: class foocontroller < application controller def home end end (there's nothing on line in actual file, blank line) is there purpose blank line? it's common use newline indicate end of line, last line. many editors (like vi, use) add in newline silently. consensus text files (especially in unix world) should end in newline, , there have been problems historically if wasn't present. why should text files end newline? the tool used count lines in file "wc" counts newlines in file, without trailing newline show 3 instead of 4. it improves readability of templates used in generator. consider: https://github.com/rails/rails/blob/master/railties/lib/rails/generators/rails/controller/templates/controller.rb to remove trailing newline, template have last line of: end<% end -%> instead of: end <% end -%> that seems less readable me. the discussion below refers ...
Read more

keyboard - Smiles and long press feature in Android -

i've created android keyboard. how add features inserting smilies, , using long keypresses display symbols? to create long-press behavior allows select several options pop-up menu, follow guide (specifically pop-up menu section): http://developer.android.com/guide/topics/ui/menus.html smileys (also known emoticons) combination of keys, understood programs , parsed same way. have do, when clicks smiley face on keyboard, simulate pressing of key combination matching emoticon. example, when clicks smiley, should simulate key presses ':' , ')' :). here's list of emoticons: http://en.wikipedia.org/wiki/list_of_emoticons
Read more

node.js - Bad Request - node js ajax post -

i'm using express 3.0 , node v0.11.0. have button submits form , i'm using ajax post data json object node server. client code is: $('#contact').submit(function(e) { console.log('submit called'); var formdata = json.stringify($('form').serializeobject()); console.log(formdata); $.ajax({ url: "http://localhost:3000/savedata/", type: "post", datatype: 'json', data: {objectdata: formdata}, contenttype: "application/json", complete: function() { console.log('process complete'); }, success: function(data) { console.log('process sucess'); }, error: function() { console.log('process error'); }, }); return false; }); then on server: var express = require('express'); var app = express(); app.set('views', __dirname + '/views'); app.set('view engine', 'jade'); app.use(express.bodyparser());...
Read more