cancel
Showing results for 
Search instead for 
Did you mean: 

Measure giving incorrect values

former_member672670
Participant
0 Kudos

Hie Guys,

I have created a combined report from 2 universes. I merged all the common dimensions. I used the below formula for the combined measure object - If (IsNull (Measure from Universe 1) Then Measure Variable from Universe 2 Else Measure from Universe 1. I got correct values for Universe 1. But, for Universe 2, I am getting values that are more than expected (incorrect). I'm not sure why it is pulling those incorrect values. I've attached the snapshots below for the measure variable from Universe 2 and the combined measure variable for both universes below. Any idea on what could be the issue? Thanks.

Accepted Solutions (1)

Accepted Solutions (1)

former_member184594
Active Contributor
0 Kudos

You problem is you formula in your Measure Universe 2,

=if [CBA Financial Payback INVST] = "No Financial Benefits" then "NFB"

elseif isnull([CBA Financial Payback INVST]) then "TBD"

else [Top Benefit - Frst Year - Fiscal BenPeriod]

The formula above returns TEXT another words string values to the report which cannot be AGGREGATED as a normal Measure. Check the values "NFB" and "TBD" in your formula. These are the problem. This won't work. Data type of measure objects should be NUMBER. Check the screen shot attached.

former_member672670
Participant
0 Kudos

I am going to explain the situation better from the start. The name of the measure object from both universe is - "First_year_benefits".


Universe 2:

There was already a report created from Universe 2 which I needed to combine with Universe 1.

I do not have access to Universe 2. "First_year_benefits" from Universe 2  is defined  as a Number Measure Type. The Measure variable is defined as a string data type. Its formula has conditions to output character values and the last condition outputs the measure object ("First_year_benefits").

I have already attached the formula above.


Universe 1:

I created this Universe based on a SQL query (MySQL). The derived table created from the SQL had all the columns that are present in the report from Universe 2 (only values are different). "First_year_benefits" was defined as a character in the database. Thus, I converted it into a Measure(number) in the universe using the "CAST" function. The parsing was successful. "First_year_benefits" only has numeric or null values in this universe. There is no measure variable/condition like Universe 2.

Thus, I need to combine the "Measure Variable from Universe 2" with "First_year_benefits" from Universe 1. Now, like u said, I changed the Measure variable to  a dimension. I also converted the "Combined variable formula', which I've attached above, to dimension from Measure. However, it gave me "Multivalue" in some of the "combined column" values for Universe 2. I've attached the necessary snapshots below. I hope we can figure this out now. Thanks.

Answers (1)

Answers (1)

Former Member
0 Kudos

Jeevan,

I am seeing you are struggling with this for quite a few days..

What Zahid said is right .. I am not sure why you are defining the formula as measure when the output is a text.. Also I am not sure how you are getting a number from universe 1..

Please post few records for all dimension used in the formula from both universe 1 and two.. and a screenshot how you are merging these two universe...  this will be helpful for us to point you towards right direction..

former_member672670
Participant
0 Kudos

Hie Durga,

I've provided all the details and snapshots in Zahid's reply. Could you please read through it and provide your feedback. I've attached a few more snapshots below just in case. Thanks.

Former Member
0 Kudos

Very difficult to read your screenshot... Can you post the values in plain text format rather ...

former_member672670
Participant
0 Kudos

Universe 1 Derived table Benefits values -

$0.31

$90,002.00

$-2,186,724.00

$250,000.00

$48,268.00

$0.00

Other values are numbers like above.

Universe 2 benefits values -

TBD

NFB

$122,520

$385,170

TBD

Other values are similar to above.

I hope that helps. Thanks.

former_member672670
Participant
0 Kudos

Any updates on how I can get the issue resolved?