cancel
Showing results for 
Search instead for 
Did you mean: 

Combining Measures when there is a string variable involved !!!

former_member672670
Participant
0 Kudos

Hie Guys,

I have the following scenario in my report -

Universe 1:

Sales Revenue (Measure)

Universe 2 :

Sales Revenue (Measure)

Sales Revenue (String Variable)

Sales Revenue (String Variable) formula contains 3 conditions -

Condition 1  - Outputs Sales Revenue (Measure)

Condition 2 -  Outputs the string "TBD"

Condition 3 -  Outputs the string "NFB"

All the common dimensions in both universes have been merged successfully.  I need to combine "Sales Revenue" from Universe 1 with

Sales Revenue (String Variable) from Universe 2.

Questions -

1) Do I need to change "Sales Revenue (Measure)" from Universe 1" to "String" dimension at the Universe level? After doing so, what formula do I need to use to combine the above mentioned objects.

2) Can I make changes at the report level instead of "Universe level". If so, how? Also, what formula do I need to use to combine the objects mentioned above.

Thanks.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Jeevan,

I would suggest you too merge Sales Revenue (Measure) from Universe 1 with Sales Revenue (Measure) from Universe 2

eg: create "Sales Rev" variable with formula  Sales Revenue (Measure) from Universe 1+  Sales Revenue (Measure) from Universe 2

then create a Sales Revenue (String Variable) on top of  Sales Rev variable at report level.


Hope this work for you.

Regards

Sunil

former_member672670
Participant
0 Kudos

What formula would I put for that Sales Revenue string variable at the end ?

Former Member
0 Kudos

Place this conditions in the Sales Revenue (String Variable) at report level.

Sales Revenue (String Variable) formula contains 3 conditions -

Condition 1  - Outputs Sales Revenue (Measure)

Condition 2 -  Outputs the string "TBD"

Condition 3 -  Outputs the string "NFB"

Regards

Sunil

former_member672670
Participant
0 Kudos

I tried the formula at the report level for the combined column name. However, it it inserting "NFB"

values for Universe 1 cells. Is there a way i can make it ignore that formula for Universe 1 cell?.

Former Member
0 Kudos

Hi Jeevan,

Can you please share the screenshot of your data and also measures with formula definition if they are not direct from database?

Regards

Sunil

Answers (2)

Answers (2)

Former Member
0 Kudos

All the common dimensions in both universes have been merged successfully.  I need to combine "Sales Revenue" from Universe 1 with

Sales Revenue (String Variable) from Universe 2.

Hi,

Create measure variables

SalesRevenueVar=[Sales Revenue (String Variable)]

SalesRevenueConvVar=If([SalesRevenueVar] InList ("TBD";"NFB");0;[SalesRevenueVar])

FinalMeasureVar=ToNumber([SalesRevenueUnv1Object])+ToNumber([SalesRevenueConvVar])

Hope it helps!

Former Member
0 Kudos

When you say "string variable", do you mean character object?

If so, why is a measure being stored as a string?

By "contains 3 conditions", do you mean that it is a case statement?

former_member672670
Participant
0 Kudos

The 3 conditions are in an if then else statement.

Here's the formula for the string type variable. I think it will make things clear.

If (financial payback) = "no financial benefit" then "NFB"

Elseif (Isnull (financial payback)) then "TBD"

Else Sales Revenue (measure)

Sales revenue from universe 2 does not have the above condition.

Do I need to change sales revenue in the above formula to char (sales revenue) so that all of them are string? I hope its a little more clear now.

Former Member
0 Kudos

If this is done as an if then else at the report level, then simply use

Else FormatNumber([Sales Revenue];"#,##0.00") as the last line.