Re: How to keep unique counts when slicing through data

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Brian (windmage_at_yahoo.com)
Date: 06/21/04 

Date: 21 Jun 2004 09:59:15 -0700

Deepak,

Thanks for you reply.

Team Count is meant to get a unique count of teams within a year. Only
one record for each team in a year is marked with 1. I am having a
problem when the user drills down the Run dimension.

Any other ideas would be appreciated. Thanks.

-Brian

    Deepak Puri <deepak_puri@progressive.com> wrote in message news:<#MMUO2aVEHA.2840@TK2MSFTNGP11.phx.gbl>...
> Is the Team Count measure intended to try and count the years in a given
> slice of the cube? If so, you should use one of the well-known
> semi-additive measure techniques:
>
> http://groups.google.com/groups?q=semi+additive+measure+group:microsoft.
> public.sqlserver.olap&start=20&hl=en&lr=&ie=UTF-8&group=microsoft.public
> .sqlserver.olap&selm=OEHKqCN9CHA.3412%40TK2MSFTNGP11.phx.gbl&rnum=25
> >>
> From: Deepak Puri (deepak_puri@progressive.com)
> Subject: Re: Average over Time Dim with MDX
> View: Complete Thread (4 articles)
> Original Format
> Newsgroups: microsoft.public.sqlserver.olap
> Date: 2003-03-27 18:22:19 PST
>
> Sounds like the classic semi-additive measure requirement. You can refer
> to this Microsoft Tech-Ed 2002 PowerPoint:
>
> http://technet.microsoft.at/includes/file.asp?ID=3336
> Slide 49>>
> 1. Average over time
>
> Sum of quantities over all months in the period, divided by the number
> of months in the period
>
> Sum( months in the period , Quantity) / Count( months in the period )
>
> Sum(Descendants( Time.CurrentMember,[Month]), Quantity) /
> Count(Descendants( Time.CurrentMember,[Month]))
>
> Avg( Descendants( Time.CurrentMember,[Month]), Quantity)
> >>
>
>
> - Deepak
>
> *** Sent via Devdex http://www.devdex.com ***
> Don't just participate in USENET...get rewarded for it!

Relevant Pages