Query on YTD Distinct Count

Is it possible to calculate " YTD Distinct Count " of number of Customers who are been serviced by a agent. We have two dimensions for period...as Year and Month . Other dimension for agent heirarchy....

"Is their any way to access fact column in my MDX qry for calculated measure?".

Thanks !!

With one time key, at the (Year)month level, and one agent key in the fact table you should be able to achive this. You will have all the keys that you will need.

Regards

Thomas Ivarsson

|||

Sasteam,

Were you able to accomplish this? I cannot get this to work for me. I keep ending up with the sum of the monthly distinct counts...which is not correct if the same customer was serviced in multiple months.

I would appreciate any sharing of info you may have.

Thanks.

|||

njojo,

I assume you are doing this with a calculated measure. If you have your measure set up as a "distinct count" measure AND use the Aggregate() function in your calculation this should work. The aggregate() function in SSAS is "aware" of distinct count measures.

|||

I can't seem to get Aggregate to work with YTD.

My measure is a distinct count: [Measures].[DC MctBrandID]

I ended up having to do this (which I found on another forum):

Count(NonEmptyCrossJoin(Descendants([MerchantBrand].CurrentMember,,Leaves)

,YTD([DateLevels].CurrentMember)

, {[Measures].[DC MctBrandID]},1))

I tried doing: Sum(YTD([DateLevels].CurrentMember),[Measures].[DC MctBrandID]).....which of course gave me the sum of the dc for each month in the year...not correct.

Then I did: Sum(YTD(Ancestor([DateLevels].CurrentMember,[DateLevels].[Year])), [Measures].[DC MctBrandID])....this number was correct but was for the whole year, not "to date"....still not right.

This is probably too much info and I apologize. I just want to do this calcuation the best possible way...my reports all have YTD and PTD requirements.

Thanks,

njojo

|||

In that case I am guessing you are using AS 2000 (sorry, I had assumed SSAS 2005)

In AS2000, the aggregate function had a couple of issues, to the point where I don't think many people actually used it and you are probably left having to use the Count(NonEmptyCrossjoin()) solution.

The Sum(YTD(Ancestor(...))) function will always produce the full year figure as the Ancestor function is returning the member at the year level, which is making the YTD() function redundant, the cube is simply returning the distinct count from the year member.

|||

I am Nitin Sharma from SASTeam. The initial person who posted this query.

I solved this by designing a dimension in my database for each Period-Type (viz. YTD, Rolling 12 months, Period). The dimension which I had designed is a view on my base Year and Month period along with a key field to identify the type of Period for which we need to calculate base measure. The eg. for dimension data is as follows:

col1 | col2 | col3 | col4 | col5 |

2006 | FEB | 2006 | FEB | 1 |

2006 | FEB | 2006 | JAN | 2 |

2006 | FEB | 2006 | FEB | 2 |

2006 | FEB | 2005 | MAR | 3 |

2006 | FEB | 2005 | APR | 3 |

.

.

.

2006 | FEB | 2005 | DEC | 3 |

2006 | FEB | 2006 | JAN | 3 |

2006 | FEB | 2006 | FEB | 3 |

Using this dimension in my cube I have linked my fact with new dimension as follows: derived year (3rd col) to fact-year and derived month (4th col) to fact-month. Actual Year dimension is linked with my new dimension-year (1st col) and actual month dimension is linked with new dimension-month (2nd col). The last col is used to define the PeriodType....1 for Simple Period, 2 for YTD and 3 for Rolling 12 month.

Now my time constraints (Period-Type) problem is solved and I can calculate any DISTINCT COUNT or any base calculation on my fact usign this logic, and hence the problem of YTD Distinct Count is solved.

I hope this works for you.

Thanks & Best Regards,

Nitin Sharma