Getting a proper COUNT

Hi,

I am new to MDX, so apologies if I am missing anything obvious and any help is greatly appreciated.

I have built a cube designed to display information on patient appointments. My only two measures are [Appointment Minutes] and [Appointment Count]. My clients want information presented in the following format:

Measures [Current Time Period] [Comparative Time Period] [% Change]

Hours Booked a hours b hours c percent

Patients Seen d patients e patients f percent

I have produced the first line of data with the following query:

WITH

MEMBER [Start Date].[Month Hierarchy].[Current] AS

'Aggregate(NonEmpty({[Start Date].[Month Hierarchy].[Date].[2006-10-11 00:00:00]:

[Start Date].[Month Hierarchy].[Date].[2006-11-11 00:00:00]}))'

MEMBER [Start Date].[Month Hierarchy].[Comparison] AS

'Aggregate(NonEmpty({ParallelPeriod([Start Date].[Month Hierarchy].[Year], 1,

[Start Date].[Month Hierarchy].[Date].[2006-10-11 00:00:00]):

ParallelPeriod([Start Date].[Month Hierarchy].[Year], 1,

[Start Date].[Month Hierarchy].[Date].[2006-11-11 00:00:00])}))'

MEMBER [Start Date].[Month Hierarchy].[% Change]

AS '([Current] - [Comparison])/[Comparison]', FORMAT_STRING = '#0.0#%', SOLVE_ORDER = 3

MEMBER [Measures].[Booked Hours] AS

'Measures].[Appointment Minutes])/60', SOLVE_ORDER = 2

SELECT {[Current], [Comparison], [% Change], [Group Average], [Benchmark %]} ON 0,

{[Booked Hours]} ON 1

FROM [Diary]

WHERE [Branch].[Branch Name].[Head Office]

This works fine. I run into trouble, though, when trying to count the patients properly in the current and comparison time columns. There is a [Patient] dimension with an [ID] attribute, and what I really want is a distinct count of how many patients have one or more appointments booked in each time column. So far, all my attempts with Distinct(), Count(), Filter(), NonEmpty() and NonEmptyCrossJoin have come to nothing. If anyone can help here, then I would be really grateful.

If you're using AS 2005, and there is a [PatientID] foreign key in the fact table, you could create a "distinct count" measure like [Patient Count] on the [PatientID] field.|||

Thanks for replying, Deepak.

That is what I have been trying to do, but I must be getting the MDX wrong.

MEMBER [No Of Patients] AS 'DISTINCTCOUNT(Filter([Patient].[Public ID], [Measures].[Appointment Count] > 0))' just returns an error. Changing DistinctCount to Count just returns 1, when I know that 12 patients should be returned by the example.

'COUNT(Filter(NonEmpty({[Patient].[Public ID].CHILDREN}), [Measures].[Appointment Count] > 0))' returns a number (the wrong one) and takes a long time to run.

Any suggestions on an expression I could use that would work?

Many thanks,

Ed.

Edit: 'COUNT(Filter(NonEmpty({[Patient].[Public ID].CHILDREN}), [Measures].[Appointment Count] > 0))' does in fact return the right result (apologies - there was an error in the test code), but takes 1 minute, 40 seconds to run. My clients are never going to accept that. I have cut dimensions and attributes down as far as I can. Can anyone suggest a way of querying the count more efficiently?

Any help greatly appreciated,

Ed.

Get percentage with variation of field values (country names)

Any help here would be greatly appreciated...

Unfortunately, data wasn't filtered prior to getting inserted into this table. Now I am stuck with cleaning it up. I have thought about writing a query to update all the values, but there are just too many variations, including spelling mistakes, so I've ruled that out as a possible solution.
I have a table which has a Country field but the values per record vary. For example US, U.S., USA, United States, UK, United Kingdom, Canada, Can, etc. I'm trying to find the percent of records per country.

Sample table data: mytable
Id Name Country
1 John US
2 James UK
3 Jane United States
4 Mary Canada
5 Jack U.S.
6 Tony United Kingdom
7 Jeff US
8 Tom Canada
9 Beth UK
10 Mark USA
I would like to show
US: 50% --> (includes any variation of US ncluding US, U.S., USA, United States)
UK: 30%
CAN: 20%
I've made several attempts myself with no luck. Thanks in advance.

You have to clean the country list first.

I would do it by retreving distinct country list and update the table for this column mannually( I mean separate updates). For example,

UPDATE mytableSET COUNTRY='USA'

WHERE Country='US'OR Country='U.S.'OR Country='United States'

These three USA names are from your sample data. This OR list will be long if you include all (mis)spellings you can find for the USA from your dirty data source.

After you have clean data, you can do something like this:

SELECT COUNTRY,count(COUNTRY)as cCount,(

CAST(count(COUNTRY)ASfloat)/CAST((SELECTcount(*)FROM countries$) ASfloat)*100)as countryPercent

FROM mytable

GROUPBY country

|||I figured the data would have to be cleaned... thanks for help with the second query, much appreciated... great help in this forum.