Formula to merge Measure objects

[former_member672670]

Hey Guys,

I posted this question a little while ago, but could not get the answer. So, I'm posting it again. I am combining 2 universes to create a combined report. Both the measure objects have "Number Data Type" in the universe. I have merged all the common dimensions in the report.

Now, the measure object from Universe 1 has been used in a Measure Variable. The variable has conditions to output some character values.  I have attached the variable formula below.  However, when I used "Measure 1" + Measure 2" formula for the combined column, I got correct results for Universe 1, but for Universe 2, it combined the character  values from Universe 1 Measure Variable with numeric values from Universe 2. I have also attached the values below.  Measure from Universe 2 does not have the variable condition.

I also tried using - If  (ISNULL(Query 1.Measure) Then Query 2. Variable Measure ELSE Query 1. Measure). This resulted in displaying the Variable character values from Universe 1 for Null Values in Universe 2.  How can I get the correct measure values from both universe?

[Former Member · Accepted solution]

Hi Jeevan, I have given the same reply in your other post also.

Here you are trying to show two measure objects values which are coming from two different universes in one column with the help of other merged objects on report.

But it is possible only when there are common records with same measure values or uncommon records for which measure values are present in only one universe(either in universe 1 or Universe 2).

Here in your case, for the common record you have two different values(e.g. Character value is coming from universe1 and numeric value is coming from Unverse2).

Hope I understand your problem here.

This is not not possible with merge dimention(because your measure variable from Universe1 is String and Measure from Universe2 is Number)

This is not poosible with  "Measure 1" + Measure 2" because it will conctenate your result.

This is only possible with merge dimention as you will get two different rows for two values which you are trying to get in on cell by doing "Measure 1" + Measure 2" but......

you have to make both the objects as dimention and datatype as string at universe level(Universe1 and Universe 2 seperatly) or by creating variables of String data type at report level.

Hope this helps

[former_member672670 · Accepted solution]

The measure object from Universe 1 is a number, but a measure variable has been created for that object which can output some character values. The ISNULL formula did work to combine the measure variable & the measure object from Universe 2, but the problem with it is that-  if the value of Measure object from Universe 2 is NULL, the formula inserts the character values from the measure variable. The formula works only if there are no null values for measure  from universe 2. There must be someway to solve this problem at the report level ???

[Former Member · Accepted solution]

Hi Jeevan,

Can you please provide sample data from universe 1 and unvierse 2 and the final output table what you want on the report. It will be easy to sort out the problem instead of juggling with the words.

e.g.

Sponser Name from Universe 1

Measure from Universe1

Measure Variable from universe 1 (formula and value)

Sponser Name from Universe2

Measure from Universe 2

And

The final output table you want on the report with the values in column.

Thanks,

Sachin

[former_member672670 · Accepted solution]

I've attached the snapshots below. I am getting correct values for all the other columns in the combined report besides the Measure - "Benefits - RT+BSR". Since I'm using the IsNull formula for the combined  measure, it is giving correct values for Universe 1 from the Measure Variable. For Universe 2, it is giving all the values, but for cells that are Null, it is putting values from the Measure Variable from Universe 1. I think I just need the correct formula for the Combined Measure variable ("Benefits - RT + BSR").

[former_member225163]

Hi Jeevan,

Please try below:

>convert the measure variable(universe 1) as detail variable

> associate with any key merge dimension

> check the result.

Cheers,

Bala

[former_member672670]

IT is still inserting values from universe 1 measure variable for Null values from universe 2.