Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Set and Measure Result conflict Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, October 31, 2013 1:26 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, November 6, 2013 6:04 AM Points: 2, Visits: 12
 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]")--YTDMEMBER [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 YTDCPAS IIF([Measures].[First Month of Year], NULL, PERIODSTODATE([Time].[Fiscal Hierarchy].[FiscalYear], Ancestor(WTDCP.item(0),[Time].[Fiscal Hierarchy].[FiscalMonth]).PREVMEMBER))SET YTDPPAS 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 logicMEMBER [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 logicMEMBER [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_YearAS Descendants(Ancestor(WTDCP.item(0),[Time].[Fiscal Hierarchy].[FiscalYear]), [Time].[Fiscal Hierarchy].[FiscalMonth])Set testas StrToMember('[Time].[Fiscal Hierarchy].&['+ CSTR([Measures].[First Invoice Date].VALUE) +']')MEMBER [Measures].[First Invoice Date1]as test.item(0).nameMEMBER [Measures].[First Invoice Date2]as StrToMember('[Time].[Fiscal Hierarchy].&['+ CSTR([Measures].[First Invoice Date].VALUE) +']').nameSET ALL_Months_Till_First_Invoiced_MonthAS PERIODSTODATE([Time].[Fiscal Hierarchy].[FiscalYear], PARALLELPERIOD([Time].[Fiscal Hierarchy].[FiscalYear], -1,test.item(0)))SET COMPYTD_SET_SUBSTRACT_CPAS EXCEPT(ALL_Months_Selected_Year, ALL_Months_Till_First_Invoiced_Month)SET COMP_YTD_CPAS 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]`
Post #1510047

 Permissions