How to create a MAX Named Set for a date dimension?

I have an OLAP cube and I need to create a Named Set to return a latest date from a date dimension. For example I have the following dimension:

[Account Period].[Account Period].[Prescription Date].[Prescription Date].12-July-2006

Based on this dimension structure, how to create a named set only to return the latest date in any grouping of the other dimensions. Or to create a named set is not the correct approach to this?

You can simply use the Tail function unless your date dimension contains dates into the future for forecasting or other measures:

Tail([Account Period].[Account Period].[Prescription Date].Members,1)

If you have dates into the future you can use a measure group to filter the dates so that only dates with data for that measure group are considered:

Tail(Exists([Account Period].[Account Period].[Prescription Date].Members,,"Sales"),1)

HTH,

Steve

|||

Thank you for your reply. I guess that it is best to put down what I really want to achieve here and for the expert to give me the best advice. I am designing a report for a pharmist on a OLAP cube. the OLAP cube consists of the following dimensions:

Measure: Count of the logical primary key (treatment count)

Dimension: Patient_ID.Patient_ID.Patient_ID

Dimension: Account_Period.Prescription_Date.Prescription_Date

Dimension: Drug.Drug_Name.Drug_Name

Dimension: Hospital.Hospital.Hospital

Dimension: Drug.Drug_Ingriedient..Drug_Ingriedient

I need an MDX query for the report so that for a selected Drug_Name (first report single parameter) and the selected Drug_Ingriedient (second report multiple parameters) the "latest" Prescription_Date of the selected Drug_Name, the "earliest" Prescription_Date of the selected Drug_Name, the duration of the selected Drug_Name given to this Patient_ID, the Drug_Ingridient at the "latest" Prescription_Date (Drug_Ingridient is one to one relationship to the Drug_Name, different Drug_Ingridient can occour on the same Drug_Name but not on the same Prescription_Date), the hospital where the Drug_Name prescripted to the Patient_ID at the "latest" Prescription_Date (Patient can be prescripted the selected Drug_Name from different hospital over the period of several years) and the total count of the Drug_Name treatment.

The dataset should consists of eight columns, Patient_ID, Latest_Date, Earliest_Date, Duration, Drug_Name, Drug_Ingridient, Treatment_Count, Hospital.

Thanks.

|||

This will be tough to do via MDX on this discussion board. There are several questions that I need to ask.

1.) What is the formula for "Duration"?

2.) Are the "Earliest" and "Latest" dates for all prescriptions given to the patient?

My email is stevepon@.microsoft.com if you want to send me information off-line. A copy of your AS project files would be a big help.

Steve