Set and Measure Result conflict

• nilamskhandare 86777

  SSC Veteran

  Points: 226

  More actions

  October 31, 2013 at 1:26 am

  #300604

  Can somebody help me with this ?

  The following 2 measures shows different result even though same logic is written for that 2 measures

  [Measures].[First Invoice Date1],[Measures].[First Invoice Date2]

  WITH

  SET WTDCP

  --AS StrToMember(@Week)

  AS StrToMember("[Time].[Fiscal Hierarchy].&[2013043]")

  --YTD

  MEMBER [Measures].[First Month of Year]

  AS ANCESTOR(WTDCP.item(0), [Fiscal Hierarchy].[FiscalMonth]).PROPERTIES("key", TYPED) = ANCESTOR(WTDCP.item(0), [Fiscal Hierarchy].[FiscalYear]).FIRSTCHILD.FIRSTCHILD.FIRSTCHILD.PROPERTIES("key", TYPED)

  SET YTDCP

  AS IIF([Measures].[First Month of Year], NULL, PERIODSTODATE([Time].[Fiscal Hierarchy].[FiscalYear], Ancestor(WTDCP.item(0),[Time].[Fiscal Hierarchy].[FiscalMonth]).PREVMEMBER))

  SET YTDPP

  AS IIF([Measures].[First Month of Year], NULL, PERIODSTODATE([Time].[Fiscal Hierarchy].[FiscalYear], PARALLELPERIOD([Time].[Fiscal Hierarchy].[FiscalYear],1,Ancestor(WTDCP.item(0),[Time].[Fiscal Hierarchy].[FiscalMonth]).PREVMEMBER)))

  -- Start for FID logic

  MEMBER [Measures].[FID] AS

  Head(

  NonEmpty(

  EXISTING [Time].[Fiscal Hierarchy].[FiscalMonth].Members,

  ([Measures].[JBA Sales Quantity])

  ),

  1

  ).Item(0).properties("key", TYPED)

  --If two brads have different FID because of different currency so puting logic to

  --get Minimum of FID out of multiple FID for diff currencies.

  MEMBER [Measures].[First Invoice Date]AS

  MIN(NonEmpty(

  (

  {[Brand].[Brand].currentmember} *

  {[Country].[Country].currentmember}*

  {[Currency].[Currency].[Currency]}

  ),[Measures].[FID]),

  [Measures].[FID])

  --End of logic

  MEMBER [Measures].[FIY] AS

  Head(

  NonEmpty(

  EXISTING [Time].[Fiscal Hierarchy].[FiscalYear].Members,

  ([Measures].[JBA Sales Quantity])

  ),

  1

  ).Item(0).properties("key", TYPED)

  MEMBER [Measures].[First Invoice Year] AS

  MIN(NonEmpty(

  (

  {[Brand].[Brand].currentmember} *

  {[Country].[Country].currentmember}*

  {[Currency].[Currency].[Currency]}

  ),[Measures].[FIY]),

  [Measures].[FIY])

  MEMBER [Measures].[Selected Year]

  AS Ancestor(WTDCP.item(0),[Time].[Fiscal Hierarchy].[FiscalYear]).properties("key", TYPED)

  SET ALL_Months_Selected_Year

  AS Descendants(Ancestor(WTDCP.item(0),[Time].[Fiscal Hierarchy].[FiscalYear]), [Time].[Fiscal Hierarchy].[FiscalMonth])

  Set test

  as StrToMember('[Time].[Fiscal Hierarchy].&['+ CSTR([Measures].[First Invoice Date].VALUE) +']')

  MEMBER [Measures].[First Invoice Date1]

  as test.item(0).name

  MEMBER [Measures].[First Invoice Date2]

  as StrToMember('[Time].[Fiscal Hierarchy].&['+ CSTR([Measures].[First Invoice Date].VALUE) +']').name

  SET ALL_Months_Till_First_Invoiced_Month

  AS PERIODSTODATE([Time].[Fiscal Hierarchy].[FiscalYear],

  PARALLELPERIOD([Time].[Fiscal Hierarchy].[FiscalYear], -1,test.item(0)))

  SET COMPYTD_SET_SUBSTRACT_CP

  AS EXCEPT(ALL_Months_Selected_Year, ALL_Months_Till_First_Invoiced_Month)

  SET COMP_YTD_CP

  AS

  IIF([Measures].[Selected Year] - 1 >

  [Measures].[First Invoice Year],

  YTDCP,

  IIF([Measures].[Selected Year] =

  [Measures].[First Invoice Year],

  NULL,

  Intersect(YTDCP, COMPYTD_SET_SUBSTRACT_CP)

  ))

  SELECT {

  [Measures].[First Invoice Date],

  [Measures].[First Invoice Date1],

  [Measures].[First Invoice Date2]

  } on columns,

  Nonempty( {[Brand].[Brand].[Brand Major].members

  * [Customer].[Country].[Country]

  * [Currency].[Currency].[Currency]}) on rows

  FROM [cube 1]