I need to create a measure that counts distinct days 
Author Message
 I need to create a measure that counts distinct days

Russ;

I need to create a measure that counts distinct days for each member within
my dataset. I then would need to create an Average Days measure that I would
push throughout all levels within the cube. But I just cant seem to make it
work??? If you have anything you can provide as far as insight on how to
resolve this -- that would be very helpful.

Patient     Days_Count     Gender

519717     2                 1

528207     1                 2

531385     1                 1

556333     2                 2

570918     2                 1

573313     2                 2

657952     2                 1

658735     2                 2

678893     1                 1

Here's what I would expect to see in the cube:

Avg Days , All Gender = 15 days count/9 patients = 1.67 Days

Avg Days , Gender 1 = 8 days count/5 patients = 1.6 Days

Avg Days , Gender 2 = 7 days count/4 patients = 1.75 Days

My cube is laid out as follows:

Dimensions:

Services

Gender

Age

Date

Fact_Table_Key (not visible)

Patient_Key (not visible)

Measures:

Patient_Key - Distinct Count

Fact_Table_Key - Count

Calculated Members:

Days - DistinctCount([Date].[Date Key].members)

Patient Days - Avg({[Patient Key].[Patient Key].members}, [Measures].[Days])
{this was my attempt at the days calc - but it doesn't work properly}

For reference Only:

      FACT_TABLE_KEY
     Patient_KEY
     DATE_KEY
     Services_KEY
     GENDER_KEY
     AGE_KEY

      1
     519717
     1832
     15788
     1
     71

      2
     519717
     1872
     60564
     1
     71

      3
     528207
     1834
     11570
     2
     74

      4
     528207
     1834
     23027
     2
     74

      5
     531385
     1678
     13673
     1
     71

      6
     531385
     1678
     55656
     1
     71

      7
     556333
     1894
     2093
     2
     63

      8
     556333
     2062
     12165
     2
     63

      9
     570918
     2019
     69700
     1
     69

      10
     570918
     2021
     51182
     1
     69

      11
     573313
     1923
     97
     2
     48

      12
     573313
     1928
     22170
     2
     48

      13
     655557
     2169
     19088
     1
     55

      14
     655557
     2218
     62370
     1
     55

      15
     657952
     1851
     53364
     1
     71

      16
     657952
     1893
     5370
     1
     71

      17
     658735
     1890
     58953
     2
     57

      18
     658735
     1949
     13879
     2
     57

      19
     678893
     1928
     3983
     1
     51

Thanks!!!



Tue, 20 Jul 2004 21:28:52 GMT
 I need to create a measure that counts distinct days

You can refer to the following white paper on distinct count issue:

OLAP Services: DISTINCT COUNT and Basket Analysis
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnol...
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Thanks,

William Chen



Sat, 24 Jul 2004 16:30:51 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. OLAP: How create a Measure with COUNT(DISTINCT Custno)

2. Aggregation takes long time with DISTINCT COUNT measure

3. Two Distinct Count measures

4. Distinct Count on Text Measure

5. Distinct Count - Aggregate Function on Measures

6. Measure Aggregation - Distinct Count

7. Measures Using COUNT DISTINCT

8. Aggregations on Distinct Count Measure

9. How-To set "Distinct(Count" measures

10. distinct count measure

11. Distinct Count on Text Measures???

12. #ERR in Analysis Browser when using Distinct Count measure


 
Powered by phpBB® Forum Software