Running Count on Distinct Values

[mixim]

Hello, One of my first posts ever and new to Cognos, please be gentle =)

Im using the User Logins table (User name and Logon time columns) from Cognos Audit. What Im looking to achieve is to look how many new users have logged in per day, also having counted this in a running total column. Purpose is to see the rate of new licences used per day.

Using the columns from Cognos Audit the list without any aggregations looks similar to this:

Date	User Name
Jan 01	Anna
Jan 01	Will
Jan 02	Jose
Jan 02	Will

So I group the dates by Day, and select a distinct count summary on User Name. This will then count unique names PER day. And if I create a running total column with summaries on each date (since its grouped by that) i'm almost there.

The problem I have is that while the distinct count will only cover unique names within one grouped entry (A day in my case), it does not consider the next day. So I want it to count distinct User Names for the whole query ( not distinct for a date) and then group those by day after

So I get a total of 4 on the above list, but it should be 3 since Will already exists in Jan 1st. Do you have any idea on how I could proceed?

Also, please do tell if I need to clear up my explenation!

Thanks!

[jeff_rob]

Hi mixim,
You will need to know if Will ever logged in before, not just that he logged in the day before.  So what you really need to see is any user who has a count of total logins equal to only one, meaning that they've never logged in before.

To do that you will have to do something like this:

Code Select Expand

select user_name, count(user_name)
  from table
where condition ... (if you have any)
   group by user_name
   having count(user_name) = 1

This query will need to be joined to a query that has user_name and day.  You can do that either in Report Studio or Framework Manager.

[mixim]

Hello Jeff, and thanks for your reply!
I see what you did there, but maybe I didn't explain it well enough.

When I run that query for lets say 1st Jan 2012 - 31st Dec 2012 I will only see a snapshot of who still had only one login until 31st of Dec 2012.

But what I would like to see is the amount of FIRST logins historically grouped for each day. And that was what I couldn't wrap my head around of how to do.  Did I explain it better now? Any ideas?

[calson33]

What database are you using? If oracle, then using their analytic functions could do the trick.

[jeff_rob]

Hi mixim,
Yes, I think I better understand what you're after, but I'm still puzzled as to how you can know when the first ever login takes place if you put a date parameter around it.  If you run it for Jan 1, 2012 through Dec 31, 2012, how do you know whether someone logged in for the first time on Jan 1st?  It would seem that you have to always consider the whole data set.

I think you can get what you need if you use the analytical equivalent of FIRST for whatever database you're using as calson33 mentioned.  In DB2 it would be FIRST_VALUE, in SQL Server you won't find it unless you're using 2012.

[Lynn]

Our good friends at BrightStar Partners who host this site have a FREE utility for license auditing that perhaps might offer the type of reporting you are attempting to do and lots more. I've never worked with it but might be worth investigating.

http://www.bspsoftware.com/Products/LicenseAuditor