How do you extract first tuple from the set?
How do you extract first tuple from the set?
1. Using could usefunction Set.Item(0)
Run a query that has Calendar year set on axis 0:
SELECT {[Date].[Calendar].[Calendar Year].Members
}
ON 0
FROM [Adventure Works]
Results:
CY 2001 CY 2002 CY 2003 CY 2004 $8,065,435.31 $24,144,429.65 $32,202,669.43 $16,038,062.60
Now take first member of the set:
SELECT {{[Date].[Calendar].[Calendar Year].Members
}.Item(0)}
ON 0
FROM [Adventure Works]
Result:
CY 2001 $8,065,435.31
Using function Tuple.Item(0)
MDX Query example:
SELECT {([Date].[Calendar].[Calendar Year].&[2003]
, [Customer].[Customer Geography].[Country].&[Canada]).Item(0)
}
ON 0
FROM [Adventure Works]
Result:
CY 2003 $32,202,669.43
Different query:
SELECT {([Date].[Calendar].[Calendar Year].&[2003]
, [Customer].[Customer Geography].[Country].&[Canada]).Item(1)
}
ON 0
FROM [Adventure Works]
Result is different:
Canada $80,450,596.98
I am new in Mdx queries and I have had a question about mdx, how can Extract first member of tuples in a set az a new set in mdx? I have a query like this:
WITH
SET [people in first date] AS
'Filter
(
{[VW Dim Customer Broker Branch].[Customer Title].[Customer Title]*[Vw Dim Date].[Shamsi Date].&[1388/06/01]:[Vw Dim Date].[Shamsi Date].&[1388/06/02]},
[Measures].[Trade Cnt]>3
) '
SET [people in second date] AS
'Filter
(
{[VW Dim Customer Broker Branch].[Customer Title].[Customer Title]*[Vw Dim Date].[Shamsi Date].&[1388/06/03]:[Vw Dim Date].[Shamsi Date].&[1388/06/04]},
[Measures].[Trade Cnt]>0
) '
/* SET [numberOfFirstItem] As
[people in first date].item(0)*/
member [measures].[numberOfPeopleInFirstDate] AS
DistinctCount([people in first date])
member [measures].[numberOfPeopleInFirstDate1] AS
Count([people in first date])
member [measures].[numberOfPeopleInSecondtDate] AS
DistinctCount([people in second date])
member [measures].[Ratio] AS
'(([measures].[numberOfPeopleInSecondtDate]-[measures].[numberOfPeopleInFirstDate])/IIF([measures].[numberOfPeopleInFirstDate]=0,1,[measures].[numberOfPeopleInFirstDate]))*100'
select
[Measures].[Trade Cnt] on 0,
[people in first date] on 1
--{[measures].[numberOfPeopleInFirstDate],[measures].[numberOfPeopleInSecondtDate],[measures].[Ratio]} on 0
/* {[Measures].[Trade Cnt]} on 0,
[people in first date] on 1*/
-- {[numberOfFirstItem]} on 0
from [DVPL]
WITH MEMBER Measures.x AS [Measures].[Internet Sales Amount] / ( [Measures].[Internet Sales Amount], Ancestors ( [Customer].[Customer Geography].CurrentMember, [Customer].[Customer Geography].[Country] ).Item (0) ), FORMAT_STRING = ‘0%’ SELECT {[Measures].[Internet Sales Amount], Measures.x} ON 0, { Descendants ( [Customer].[Customer Geography].[Country].&[Australia], [Customer].[Customer Geography].[State-Province], SELF ) } ON 1 FROM [Adventure Works]
