October 30, 2013 at 2:56 am
Hello Experts,
Can you please suggest me the alternate solution of following MDX query issue please its very Urgent:
The following set of COMP_YTD_CP is works fine with single brand but when user selectes multiple brand it always takes min value of the First Invoice Month irrespective of current memebre of the brand
For e.g: Brand First Invoice Month
B1201202
B2 201107
B3 201101
B4 201204
In this case, for all the brands first invoice date is considerd as 201101 inspite of different first invoice dates mentioned above next to the brand.
WITH
SET SP
AS STRTOMEMBER(“[Time].[Fiscal Hierarchy].&[201204]”)
MEMBER [Measures].[First Invoice Year] AS
Head(
NonEmpty(
EXISTING [Time].[Fiscal Hierarchy].[FiscalYear].Members,
([Measures].[Sales Quantity])
),
1
).Item(0).properties("key", TYPED)
SET ALL_Months_Selected_Year
AS Descendants(Ancestor(SP.item(0),[Time].[Fiscal Hierarchy].[FiscalYear]), [Time].[Fiscal Hierarchy].[FiscalMonth])
//--Getting Month previous to the First Invoice Month and then getting PeriodsToDates
SET ALL_Months_Till_First_Invoiced_Month
AS PERIODSTODATE([Time].[Fiscal Hierarchy].[FiscalYear],
PARALLELPERIOD([Time].[Fiscal Hierarchy].[FiscalYear], -1,
Head( NonEmpty( EXISTING [Time].[Fiscal Hierarchy].[FiscalMonth].Members,
([Measures].[JBA Sales Quantity]) ), 1).Item(0)))
SET COMP_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],
SP.item(0) ,
IIF([Measures].[Selected Year]=[Measures].[First Invoice Year] ,
NULL,
INTERSECT(SP, COMP_SET_SUBSTRACT_CP))
)
MEMBER [MEASURES].[SALES YTD CP COMP]
AS SUM(COMP_YTD_CP, [Measures].[Revenues])
SELECT
{[MEASURES].[SALES YTD CP COMP]} ON COLUMNS,
Nonempty( {[Brand].[Brand].[Brand Major].members
* [Customer].[Country].[Country]* [Currency].[Currency].[Currency]})on rows FROM [Cube1]
October 30, 2013 at 4:27 am
Firstly, for the love of all that is holy please format your code and put it in a code block before posting it here 😀
WITH
SET SP AS
StrToMember([Time].[Fiscal Hierarchy].&[201204])
MEMBER [Measures].[First Invoice Year] AS
Head
(
NonEmpty
(
(EXISTING
[Time].[Fiscal Hierarchy].[FiscalYear].MEMBERS)
,[Measures].[Sales Quantity]
)
,1
).Item(0).Properties("key"
,TYPED)
SET ALL_Months_Selected_Year AS
Descendants
(
Ancestor
(
SP.Item(0)
,[Time].[Fiscal Hierarchy].[FiscalYear]
)
,[Time].[Fiscal Hierarchy].[FiscalMonth]
)
//--Getting Month previous to the First Invoice Month and then getting PeriodsToDates
SET ALL_Months_Till_First_Invoiced_Month AS
PeriodsToDate
(
[Time].[Fiscal Hierarchy].[FiscalYear]
,ParallelPeriod
(
[Time].[Fiscal Hierarchy].[FiscalYear]
,-1
,Head
(
NonEmpty
(
(EXISTING
[Time].[Fiscal Hierarchy].[FiscalMonth].MEMBERS)
,[Measures].[JBA Sales Quantity]
)
,1
).Item(0)
)
)
SET COMP_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]
,SP.Item(0)
,IIF
(
[Measures].[Selected Year] = [Measures].[First Invoice Year]
,NULL
,Intersect
(
SP
,COMP_SET_SUBSTRACT_CP
)
)
)
MEMBER [MEASURES].[SALES YTD CP COMP] AS
Sum
(
COMP_YTD_CP
,[Measures].[Revenues]
)
SELECT
{[MEASURES].[SALES YTD CP COMP]} ON COLUMNS
,NonEmpty
(
{
[Brand].[Brand].[Brand Major].MEMBERS*
[Customer].[Country].[Country]*
[Currency].[Currency].[Currency]
}
) ON ROWS
FROM [Cube1];
[/Code]
That's better.
Right, this query is pretty static. How are the users viewing the results (excel, a parametrized report etc.) as this will help us answer your question?
On first glance your calculations and sets don't appear to take brand into account. This could be it. However without knowing how the cube is being accessed (and the query that is being sent to the cube) it's difficult to tell.
Download MDX Studio for formatting and much more.
October 30, 2013 at 8:33 am
Thank you so much sir for response.
This is parameterized query to use in SSRS.
Also as suggested, we are trying make the cross join with Brand in Set but we are getting following issue:
The [Measures].[First Invoice Date1] and [Measures].[First Invoice Date2] shows different result even the value passed is same
==============================================================
Code:-
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
==================================================================
FYI:
The value passing in string [Measures].[First Invoice Date] = 201207 (I.e. July 2012)
But from viewing test set using the [Measures].[First Invoice Date1] shows the Jan 2011 But the [Measures].[First Invoice Date2] shows the correct value as July 2012
Regards,
Amit J
October 30, 2013 at 8:57 am
Hi,
We are using following code for measure:
MEMBER [Measures].[FID] AS
Head(
NonEmpty(
EXISTING [Time].[Fiscal Hierarchy].[FiscalMonth].Members,
([Measures].[Sales Quantity])
),
1
).Item(0).properties("key", TYPED)
MEMBER [Measures].[First Invoice Date]AS
MIN(NonEmpty(
(
{[Brand].[Brand].currentmember} *
{[Customer].[Country].currentmember}*
{[Currency].[Currency].[Currency]}
),[Measures].[FID]),
[Measures].[FID])
Note:- In [Measures].[FID] it shows the different Invoice Month for diffrenet currencies irrespective of same brand and country so user requested to get min of FID so created 2nd [Measures].[First Invoice Date].
Regards,
Amit
October 30, 2013 at 9:12 am
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(
(
{[Articles].[Coalition Brand].currentmember} *
{[Customer].[Sold To Country].currentmember}*
{[Local 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(
(
{[Articles].[Coalition Brand].currentmember} *
{[Customer].[Sold To Country].currentmember}*
{[Local 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]
October 30, 2013 at 9:16 am
Hello
I'm sorry don't know how to past query under code block, is this someting under under IFCode (XML code)? ?
October 30, 2013 at 9:20 am
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(
(
{[Articles].[Coalition Brand].currentmember} *
{[Customer].[Sold To Country].currentmember}*
{[Local 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(
(
{[Articles].[Coalition Brand].currentmember} *
{[Customer].[Sold To Country].currentmember}*
{[Local 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]
October 30, 2013 at 9:25 am
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]
October 31, 2013 at 1:58 am
Hi,
Did you get chance to find the issue with First Invoice Date measure please?
Regards,
Amit J
October 31, 2013 at 4:04 am
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 🙂
October 31, 2013 at 6:40 am
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
October 31, 2013 at 6:53 am
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?
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply