Can't merge measures ...

Hi All,

I’m using Webi 4.2 Support Pack 7 Patch 4

I realise it’s not possible to merge measures, however …

I’m trying to calculate Z scores for weight-for-age and weight-for-length for about 2,000 under-5-year-old children.

I have added a query (from an excel file) with reference tables from the World Health Organisation in the report, with the expected means and standard deviations of weight for each age (in the case of weight-for-age) and length (for weight-for-length). These are then used to calculate the Z scores, which are a standardised measure to show how far a child is from the expected mean.

That all works fine for weight-for-age, as I can merge the age of the child (a dimension) with the age dimension in the reference table.

However, the variable from the universe holding each child’s length/height is a measure - I can’t do anything about this (I don’t think) - so I can’t merge it with the length dimension in the reference table.

I’ve tried converting the length measure to a dimension, but I can’t merge a declared variable with a dimension.

I changed both the length measure and the length dimension for the reference table to dimension variables, but BI would not let me merge two declared variables.

Is there any other way I can align the two queries so my Z scores can calculate?

I’ve run out of ideas, any help greatly appreciated.


SeamusObstat (BOB member since 2017-04-27)

try to convert the measure-value into string-format in a new variable and merge these variables

n8aktiv :de: (BOB member since 2018-12-29)

OK, interesting idea - although I need it as a number to calculate the z scores

SeamusObstat (BOB member since 2017-04-27)

You may need to work around by running through at least one variable and coercing it one way or another into working as a dimension. I ran into this issue when I needed to have a measure display as a dimension for a chart in WEBI. Expect to see numerous issues, but you’ll want to play with

  1. Just trying to put the measure into a new variable as a dimension, and if this results in errors playing with the context using IN, FOREACH, And FORALL where applicable
  2. Converting the variable to text in one variable and then back into a number in another variable (wonky for sure but I’ve done it before with varying success).
  3. Breaking down and requesting your staff on the universe side to design a solution for you that is going to work sustainably. Some things with BOE just have to be resolved at the universe level, but it’s definitely worth fighting to make work if you can!

After reading over you post again I’ve also come to another idea for you which is to use an if statement - I believe I’m understanding that you have z scores from 2 sources and you can’t merge them.

If you can merge on some identifier object you can create a new measure which gets the z score from source 1 if it is source 1 and z score from source 2 if source 2.

Do you have ID’s? If so you can merge ID’s to get the subjects all into one table then you can do the following if statement:

If(not(isnull([zscore1]))) then [zscore1] elseif(not(isnull([zscore2]))) then [zscore2]

Then you have all measures in the same column and all ids in the same column respectively, you can then make age a detail on the ID and you’ll be good to go I believe.