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 ««12

MDX query set does not display correct value Expand / Collapse
Author
Message
Posted Wednesday, October 30, 2013 9:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 2:36 AM
Points: 28, Visits: 78
Hello

Please ignore above code, it is consfusing with this please get a look on following code


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

Post #1509838
Posted Thursday, October 31, 2013 1:58 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 2:36 AM
Points: 28, Visits: 78
Hi,

Did you get chance to find the issue with First Invoice Date measure please?

Regards,
Amit J
Post #1510054
Posted Thursday, October 31, 2013 4:04 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 505, Visits: 985
I think your issue is with this:
MEMBER [Measures].[FID] AS 
Head(
NonEmpty(
EXISTING [Time].[Fiscal Hierarchy].[FiscalMonth].Members,
([Measures].[JBA Sales Quantity])
),
1
).Item(0).properties("key", TYPED)

Since you are using .item(0) and HEAD,1 you are limiting the result to only one member. So when the user picks more than one brand it will always be the earliest FID that it can grab from the brand members. You need to integrate brand into your initial calculation rather than splitting it up as you are now.

Best of luck





I'm on LinkedIn
Post #1510088
Posted Thursday, October 31, 2013 6:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, December 4, 2013 2:36 AM
Points: 28, Visits: 78
Hi,

I'm confused... because FID is passing the correct value I.e July 2012 in both the measures but while displaying this via [Measures].[First Invoice Date2] shows correct result (I.e. July 2012) but it gives the wrong value (Jan 2011) with the [Measures].[First Invoice Date1] :-(

Again the [Measures].[First Invoice Date2] displays value using set test but the [Measures].[First Invoice Date1] directly used
StrToMember('[Time].[Fiscal Hierarchy].&['+ CSTR([Measures].[First Invoice Date].VALUE) +']')


MEMBER [Measures].[FID] AS 
Head(
NonEmpty(
EXISTING [Time].[Fiscal Hierarchy].[FiscalMonth].Members,
([Measures].[JBA Sales Quantity])
),
1
).Item(0).properties("key", TYPED)

MEMBER [Measures].[First Invoice Date]AS
MIN(NonEmpty(
(
{[Articles].[Coalition Brand].currentmember} *
{[Customer].[Sold To Country].currentmember}*
{[Local Currency].[Currency].[Currency]}
),[Measures].[FID]),
[Measures].[FID])

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



Regards,
Amit J
Post #1510151
Posted Thursday, October 31, 2013 6:53 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 8:53 AM
Points: 505, Visits: 985
My last reply was concerning your original problem (displaying incorrect values when more than one brand is selected). It's difficult for me to test anything without having access to your underlying data. Can you recreate the problem in AdventureWorks?




I'm on LinkedIn
Post #1510154
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse