Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Set and Measure Result conflict Expand / Collapse
Author
Message
Posted Thursday, October 31, 2013 1:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 06, 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]")

--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]

Post #1510047
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse