pls how to use MeasureExpression?

Question

Post reply

pls how to use MeasureExpression?

doug moore

Mr or Mrs. 500

Points: 520
More actions
September 16, 2011 at 7:01 am

#390420

what is the syntax to use MeasureExpression.
right click a Fact and hit properties. over in the right is MeasureExpression. where is a sample syntax to use that?
also is it possible to add stdev to the aggregateFunction list?
i was suppose to have this working already :0
doug

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 1

sample measure expression

Measure1 is called Sales

Measure 2 is called Increase

For Measure 1, expression could be

Sales * Increase

Basically does what in the old world, we used to call 'before rollup' ie sum of (A x B), not SUm(A) x Sum(B)

See here for a much better run through --> http://cwebbbi.wordpress.com/2005/06/30/measure-expressions/

Also note the link from Chris' site to Richard T's whitepaper, you may want to follow that also.

You could prob use a calculated measure to do your stddev.

Steve.

doug moore Mr or Mrs. 500 Points: 520 More actions · Answer 2

by calculated you mean the Calculations tab then that entry takes a {set} as the parameter for stdev.

stdev({set}, expression) and typically you put in a date field here and it aggragates data accross the dates.

what i am looking for is to aggragate the data of a column for each single date seperately.

just like when you click of a Fact and you select an aggregateFunction of MIN

that gives me a MIN of the single column of that data that is aggregated.

i tried in calculations to put the same name into both fields but get error values

stdev([stddevValue], [stddevValue])

this when i created Fact > stddevValue as AggregateFunction = None

now i see what you mean for MeasureExpression only takes simple expressions and no aggregate as i tried

stddev(StddevPctValue) and it said it only accepts * and /

so lastly it looks to me like i need the stdev added to Fact > AggregateFunction list

doug

doug moore Mr or Mrs. 500 Points: 520 More actions · Answer 3

ok moving back to the Calculations area i think i see what is needed

stddev({set}, {expression})

my {set} needs to be a dimension that I am grouping by.

so in my case I have tried

stddev([Dim Measure Groups].[All],[StddevAvgValue])

and when i run a query in MDX i get all -1.#IND for each date

do i need to check for NULL or IsEmpty on [StddevAvgValue] ?

is that why i am not getting values?

I think i am close

doug

doug moore Mr or Mrs. 500 Points: 520 More actions · Answer 4

min([Dim Measure Groups].[All],111) gives "111.0"

stdev([Dim Measure Groups].[All],111) gives "-1.#IND"

so i am not sure where my error can be.

i run an MDX of it with just 1 guys data with his data row and the stdev row

i get correct values for his row and -1.#IND for the stdev rows

same thing for when i hardcode a value into it like above 111 - i still get that error.

just had a thought that my columns are [report dates] and it is my rows ("ON ROWS") that has [Dim Measure Groups]

does stdev only work on a column name that is in the MDX "ON COLUMNS" ?

doug

doug moore Mr or Mrs. 500 Points: 520 More actions · Answer 5

ok so i figured out ...[All] or .Siblings is a grouping and stdev needs a {set}

so i used stdev({Descendants([Dim Provider].[Dim Provider].Siblings) },...

and Descendants gives me all the child items as a set.

only i want to omit .[All] which is showing too. is there a way to omit hardcoded 1 item in list (in my case [Dim Provider].[Dim Provider].[All])?

so i am getting values now which are 0. at least no errors.

is there a way to exclude data that is empty or NULL ?

i currently have

iif((IsEmpty([StddevAvgValue]) or [StddevAvgValue] is null),0,[StddevAvgValue])

but that is changing missing data to 0 which might mess up results.

doug

pls how to use MeasureExpression?

Cookies on SQLServerCentral