Complex expression to calculate the average of a measure

[#1]

Hi Everyone,

I like to build an expression in a chart similar to this below but just slightly more complex:

AVG( {$<PatientType = {'Inpatient'}, PatientDayCount_NICU = {1} >} (AdmitDate-DischargeDate))

The expression is supposed to calculate the average of a subtraction between the AdmitDate and the DischargeDate. Since the underlying fact table contains a couple of rows which must not be included I need the other filters as well. For instance the Patienttype must be 'Inpatient' etc.

I also have duplicates in there so I will need to group the records based on a specific key column. I think that can be accomplished with a "distinct" somewhere in the expression above. Don't know where!

Another filter I need to include should

a.) Only include records with a DischargeDate unequal to 01/01/1900 which is the default for Null in the underlying fact table.

b.) And include records with a DischargeDate between today and today - 30 days.

Any kind of hint is very much appreciated.

Thanks

Ludwig

[#2]

Just a couple thoughts on that...

I think we are going to aggregate our fact data to not having such complex expressions which are kind of hard to develop and debug. This will make the qvw files bigger since basically the same data are in their twice but makes it easier to maintain and develop.

What's your thoughts on that?

I also play with the idea to spread them out into serveral qvw files and link the files so the fact tables can be designed for specific measures. A user would not notice this. He just jumps from one qvw file to another but each of them has a separate data model.

Thx

Ludwig