Need Measure calc help 
Author Message
 Need Measure calc help

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



Tue, 20 Jul 2004 21:23:12 GMT
 Need Measure calc help

You will need to use a combination of the SUM and COUNT functions in order
to get the average as you're trying to get it in the example below.  In the
foodmart 2000 cube, there's an example of how to do this I believe with a
calculated member called sales average.

--
Sean

This posting is provided "AS IS" with no warranties, and confers no rights.


Quote:
> 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



Wed, 21 Jul 2004 02:33:57 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. need help with report calc and query calc

2. Calc. Measure, Solve Order

3. How do I make all base Measures visible and have calc members over them

4. Calc. member using dimension and measure

5. puzzler - calc measure

6. Problem with Calc Measures and Custom Rollups

7. Calc Measure-Solve Order

8. Calc Member formed from one measure accross Dims

9. EQ with calc measures - "unspecified error"

10. Adding calc measures to virtual cubes with dso

11. Need help with Calc member...sum of maxes of all descendants

12. need help with date calc...


 
Powered by phpBB® Forum Software