how to get max min date from a tuple

Question

suppose i have a bunch of people (with dimension) they have a bunch of claims (dimension)

and in each claim there is a bunch of records with amount and date!

those records/transactions form a cube with measures like amounts paid

Ultimately i need to get the average time between first and last payments (claim trail length)

for each person (adjuster)

but I cannot even get to extract the first (min) and last (max) transaction date for each person/claim set

with member LastDate as

!!!!! max date with the highest key perhaps???

but need to get inside tupe of person/claim

SELECT

{MEASURES.[LastDate]}

ON 0,

     ([Person].[Hierarchy].[Person Name].members, 

	 [Claims].[ID].members),

ON 1

FROM [mycube]

Thanks!!!!

Answer 1

Hi，greg lipman

• define 2 calculated members : [Measures].[FirstPaymentDate] and [Measures].[LastPaymentDate].
• use the Min and Max to find the earliest and latest payment dates for each claim.
• NonEmpty function is used to ensure that only dates where a payment has occurred (i.e., where [Measures].[Amount] is not empty) are considered.
• The SELECT statement determines the data displayed through columns (COLUMNS) and rows (ROWS). Here, [Measures].[FirstPaymentDate] and [Measures].[LastPaymentDate] are placed on the columns.
• The rows are defined by the Cartesian product of [Person].[Hierarchy].[Person Name].MEMBERS (all member names of personnel) and [Claims].[ID].MEMBERS (all members of claim IDs).
• The NON EMPTY keyword is used to exclude combinations in this Cartesian product that have no payments, thereby only displaying combinations that have at least one payment record.

WITH
MEMBER [Measures].[FirstPaymentDate] AS 
  Min( 
   NonEmpty( 
      [Claims].[ID].CurrentMember * [Date].[Date].Members,
      [Measures].[Amount] 
          ),
     [Date].[Date].CurrentMember
     ) 
MEMBER [Measures].[LastPaymentDate] AS 
   Max( 
    NonEmpty( 
      [Claims].[ID].CurrentMember * [Date].[Date].Members,
      [Measures].[Amount] 
          ), 
     [Date].[Date].CurrentMember 
     ) 
SELECT { 
[Measures].[FirstPaymentDate], 
[Measures].[LastPaymentDate] 
       } 
ON COLUMNS, 
NON EMPTY (
    [Person].[Hierarchy].[Person Name].MEMBERS, 
     [Claims].[ID].MEMBERS 
) 
ON ROWS
 FROM [mycube]

---

Regards

Mikey Qiao If you're satisfied with the answer, don't forget to "Accept it," as this will help others who have similar questions to yours.

Answer 2

Mikey

first and foremost thanks for your response nobody to ask at all learn myself

Before anything i have an (existential) question

I look thru your code and i (again in 10th time) go to the MSDN docs to check on Max or Min

and there i see:

Syntax

Max( Set_Expression [ , Numeric_Expression ] )

In your code i see something much more versatile

Max(set, date member)

where do i get that info?