October 30, 2013 at 2:57 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:51 am
Please do not crosspost. It wastes peoples time and fragment replies.
Original thread with replies can be found here:
http://www.sqlservercentral.com/Forums/Topic1509687-17-1.aspx
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply