Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Calculated measure used in another calculated measure

Question
1
Views
0
Comments
JessicaAZ72
JessicaAZ72 Rank 1 - Community Starter

I created a calculated measure. Calculated Measure 1, the column in the report is called Account.  It is a Case statement but very long and works well.

Now I want to use this calculated measure for another calculated measure, Calculated Measure 2.  The problem is when I select it, the entire case comes over as part of my new calculated measure.  Is there a way to prevent that?  I am finding that it doesn't work in the calculation.

Calculated Measure 1

CASE

WHEN LEFT("Natural Account"."Account Code",1) = 6 THEN "Natural Account"."Account Code"

WHEN "Natural Account"."Account Code" = 140400 THEN '612015'

WHEN "Natural Account"."Account Code" = 151403 THEN '656203'

WHEN "Purchase Order"."Category Name" = 'IT Use Only - HW > $5,000' THEN '656013'

WHEN "Purchase Order"."Category Name" = 'IT Use Only - SW > $5,000' THEN '612015'


END

The new calculated measure, I want to CONCAT this column and another but when I select the first calculated measure, it brings over the entire case statement and then does not work in the formula.

Other column "Cost Center"."Cost Center Code", not named by me, part of the data model and comes over this way when I select the column.

The way I thought it should be

CONCAT("Account","Cost Center"."Cost Center Code")

I am just trying to string the results together

6120150535

The problem is Account is a calculated measure so when I select it what really comes over is this

CONCAT("

CASE

WHEN LEFT("Natural Account"."Account Code",1) = 6 THEN "Natural Account"."Account Code"

WHEN "Natural Account"."Account Code" = 140400 THEN '612015'

WHEN "Natural Account"."Account Code" = 151403 THEN '656203'

WHEN "Purchase Order"."Category Name" = 'IT Use Only - HW > $5,000' THEN '656013'

WHEN "Purchase Order"."Category Name" = 'IT Use Only - SW > $5,000' THEN '612015'
END

","Cost Center"."Cost Center Code")

Is there a way for me to select the first calculated measure without Oracle needing to redo the calculation?