Creating a named calculation in a dimension based on an attribute on another dimension

• ertemen

  SSC-Addicted

  Points: 479

  More actions

  October 4, 2012 at 4:32 am

  #282617

  Hi All,

  I am trying to create a named calculation in one of the dimensions (financials dimension) as follows

  case

  when PureYOA = '[?]' then '[?]'

  when PureYOA < left(convert(varchar,dwdim.DimDate.MonthKey),4) then PureYOA

  else left(convert(varchar,dwdim.dimdate.MonthKey),4) end

  The monthkey that is referred within the calculation is in DimDate dimension

  and I get the following message. Please see the screen shot

  How can I refer to a column in another dimension when creating a named calculation?

  Thank you

  Enis

• ertemen

  SSC-Addicted

  Points: 479

  More actions

  October 4, 2012 at 5:06 am

  #1545445

  Guys,

  It turns out that we could just write a SQL snippet and wrap up with paranthesis as it is an expression.

  Below is working

  (select case

  when PureYOA = '[?]' then '[?]'

  when PureYOA < left(convert(varchar,C.MonthKey),4) then PureYOA

  else left(convert(varchar,monthkey),4) end

  from dwdim.dimfinancials A inner join dwfact.factpremiumcash B on

  A.financialskey = B.FinancialsKey inner join dwdim.dimdate C on B.transactiondatekey = C.DateKey)

  If there is any better way of doing it, please let me know.