Post New Web Links

Distinct Count

Posted By: Posted Date: April 10, 2011 Points: 0 Category :

Hi,

I created a basic Distinct Count Measure for my customers - i.e. a customer distinct count using the default measures created in Analysis Services 2008 (not a calculation).

It works perfectly however, when used in conjunction with the below;

CREATE MEMBER CURRENTCUBE.[Time].[Time By Month].[Year-To-Date (Month)]
AS SUM(YTD(strToMember("[Time].[Time By Month].[month].&[" + str(year(Now())) + "-" + format((month(Now())), "00") + "-01T00:00:00" + "]").LAG(1)),[Measures].CurrentMember),
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Time';

View Complete Post

More Related Resource Links

How can I GROUPBY Branch, then by Client and then by Referral Type and COUNT DISTINCT LPS # in JavaS

Hi, I have an array in JavaScript. How can I GROUPBY Branch, then by Client and then by Referral Type and COUNT DISTINCT LPS # in JavaScript. Please note I need to do this client side using JavaScript. Array Data: Branch Client Referral Type LPS # 402036 402430 Psychological File Review 30 402049 402805 In-Home Assessment 10.87927 402050 402993 Chiropractic Assessment 100 402049 402805 Chiropractic File Review 10.88054 402049 402806 Chiropractic File Review 10.88055 402049 402806 Defense Medical 20 402049 402807 Chiropractic Assessment 10 402049 402807 In-Home Assessment + Form 1 10.88054 402049 402807 Physiotherapy Assessment 10 402049 402808 Attendant Care Assessment 10 402049 402808 Chiropractic Assessment 10.88041 402049 402808 In-Home Assessment 10 402049 402816 Chiropractic Assessment 10.85316 402049 402827 Neurological File Review 10.88047 402049 402827 Social Work File Review 10.88044 402050 402605 Psychological File Review 880434 402050 402661 Physician Assessment 878203 402050 402993 Physician A

SSAS 2008 Measure group Distinct count

Hi all, I have a data of as number of trasactions,DD, SO, BouncedDD, CancelledDD all are in count (number) while adding these measures manullay to a measure group I have selected usage as DistinctCount for one measure and for all the remaining measures as DiscinctCount.While deploying the cube it shows error as "Fact table canot have more than one distinct count"

MDX count distinct calculation where another column

Hi All,

i have a fact table like the following

1-episodeKey (not unique)

2-Readmission (takes value null or 'readmission' may i chaneg to (0 or 1) if that help solve the problem)

3-date

4-other dimesnions foreign keys

i want to count distinct episode keys where readmission is not null,

so how can i write a calculation that make that count ?

Ahmed Salah

How to implement distinct count with AND/OR clause?

Hi,

I'm trying to implement distinct count in MDX together with OR clause.
For instance:

I have two "questions" that I want answered via MDX.

1) Number of customers that are individuals and have at least 1 transaction

2) Number of customers that are from Serbia and have at least one arrangement

mdx 1:

with Member segmentCount as
   Count(
    Filter(
			[Customer].[Customer].Children,[Measures].[TransactionCount] > 0)
		) 
		
Select segmentCount on columns FROM [CPM] 
WHERE ([Customer].[IsIndividual].[IsIndividual].&[1])

result: 35572

mdx 2:

with Member segmentCount as
	 Count(
		Filter(
			[Customer].[Customer].Children,[Measures].[ArrangementCount] > 0))
			
Select segmentCount on columns FROM [CPM] 
W

Distinct Count - MDX

I have requirement to count of number of new products which are been sold in current quarter with respect to previous quarter.

For ex: if i have sold 10 unique products in 2010Q1 and in 2010Q2 14 product are been sold in which 4 product are newly introduced...so i want the end result count to be 4.

Regards
Sunil

AGGREGATE over a time period: how to behave like a distinct count ? (aggregate on the full period an

I would like to compute a measure "MyMeasure" over the last 12 months :

Aggregate({[Event DATE].[Calendar].CurrentMember.Lag(11):[Event DATE].[Calendar].CurrentMember},[Measures].[MyMeasure])

MyMeasure is not a distinct count, so the results returned is the SUM of the distinct count performed on each month over the last 12 month.

What I would like to have is the distinct count over a full year (all months taken together).

For example, suppose that MyMeasure is a simple measure that always returns 1.

The aggregation returns 12 (1 for each month then SUM). I would like it to return 1 (measure on the whole period).

Can somebody help me that's really a very big issue I have to fix !

Thanks

calculated member-distinct count

I have a product dimension table whose level is category-brand-product id. I have a fact table at lowest level product id and measure sales amt which can have positive and negative values.

I need distinct count of brands for a particular category whose sales is greater than 0.

fact table data:                                   product table
productid salesamt                              category brand   productid
1             50                                          c1         b1         1
2             100                &nb

Distinct Count with Filtering IDs

Hi,

Please bear with me as I am newbie in SSAS/MDX. I want DISTINCTCOUNT on ID column of my fact table but after filtering out -1 i.e. unknown IDs.
Here are my tables

DimStudent:
---------------
ID Name
----------------
-1 Unknown
1   Joe
2   Tim
3   Paul
4   Zina

DimCourse:
----------------
ID Name
-------------------
-1    Not Registered
11 Maths
22   Science
33   Arts

FactStudent:
---------------
DimStudentID  DimCourseID  Marks
-----------------------------------------------------------------
1                    11                   89
1                    22                   87
1                    33       &n

Distinct Count on column

Hi I have this Query which is getting me good results ...

SELECT

CASE WHEN rn = 1 THEN o_Error_Stmt ELSE '' END AS Error_Stmt,

CASE WHEN rn = 1 THEN o_Error_Number ELSE '' END AS [Error_Number],

CASE WHEN rn = 1 THEN o_Count ELSE '' END AS [Count],Membership_Number

INTO Stage2_Processing_Errors_Final_Result

FROM

(

SELECT o_Error_Stmt, o_Error_Number,O_Count,Membership_Number

,ROW_NUMBER() OVER(PARTITION BY o_Error_Stmt, o_Error_Number ORDER BY Membership_Number) AS rn

From

(

Select Distinct

Error_Stmt AS o_Error_Stmt,

CAST([Error_Number] AS varchar(15)) AS O_Error_Number,

CAST (COUNT(*) OVER(PART

Select Distinct with Count

This is something I can easily do in SQL, but I'm pretty new to LINQ and haven't been able to find an answer online.

I need a LINQ query that returns two values; the first a distinct value and the second the count of the first value. This is filtered by a parameter. Here is my table:

ID	City	State
1	Portsmouth	Hampshire
2	Portsmouth	Hampshire
3	Southampton	Hampshire
4	Brighton	East Sussex

In SQL I use the following:

SELECT DISTINCT City, COUNT(*) AS Expr1
FROM My_Table

WHERE State = @State

If @State is equal to Hampshire I get the following result

Portsmouth	2
Southampton	1

I have managed to create a query that brings back a distinct City;

var recordset = (from s in MyTable where s.State == state select new { s.City,&nb

DISTINCT COUNT #VALUE

I want to do a DISTINCT COUNT of Subsbribers who have desposited over £450. I am trying this but just get the #VALUE Error

DISTINCTCOUNT(FILTER([Subscriber].[Subscriber].[Subscriber Key], [Measures].[CardDepositAmount] > 450))

Any ideas?

Thanks

Same measure group for count and distinct count in SSAS

Hello there,

I need to make DistinctCount aggregation of few columns in the same fact table in SSAS. For each DistinctCount it creates a new measure group. I need to put all these under same measure group. Please let me know how to go ahead.

Regards,

Palash

How to create a new measure group for distinct count measure?

There is a warning info on my distinct count measure saying "break distinct count measure to a new group to improve performence...", but When I create a new measure group, I cannot choose the fact table which contains the distinct count measure field, as there is already a measure group using this fact table.

Then how could I remove this warning info?

Regards! directfriends.net

Print row count on Label

Hi,

I have an SP that simply performs a COUNT(*) from a table. I'm am trying to print that number in an ASP Label. It works in Query analyzer but I cant wrap my head around how to display it in a label. Any guidance much appreciated.

Thank you.

Count words in the ascx file

Hello!

I have 163 .resx files. I want to count the words in these files, because my want to translate the sentences (my task is the localisation).

Now I'm opening each file in Ms Visual Studio and I count words manually. How can I count words in all files? And the second question: can I use a program what grab strings from .resx into an editor where I can translate them and save bacn into .resx files? Or must open the files one by one and edit the text fields manually?

Thanks for helping, unfortunately I'm not a programmer.

How to display a count of search results with keywords in a label?

Sorry for a basic post here; I am very new to VB!

I am currently building a search page, which includes a textbox and two drop-down lists, a search button, and a datagrid. If results are found and displayed, I would like a label to display:

(e.g.) "24 results found for 'blue sky clouds' "

...where the integer represents the results count, and the keywords and/or drop-down list selections are included in the string.

If no results are found I would like the label to display:

(e.g.) "No results were found that match your selection. Please try again"

I would also like the label to be invisible until a search occurs.

Thanks in advance!

Editor's Note: Rich Clients Count Too

It's the dead of winter here at the MSDN Magazine offices, and we're having a blast. Temperatures are making their daily struggle to stay above zero. The tempting aroma of chestnuts roasting on an open subway ventilation grate fills the city with a wintry mix of excitement and anticipation.

MSDN Magazine February 2003


Categories:

ASP.Net	Windows Application	.NET Framework	C#	VB.Net	ADO.Net
Sql Server	SharePoint	Silverlight	Others	All