I have a loginhistory table. I need to measure on how many different days each user logged in. And ignore those times they logged in multiple times during a day.
So, by doing a distinct count by day, I get my correct result. Lets say, 7 different days, with each day having a value of 1. But now I want to aggregate those 7 into a single number for the month of September.
It feels like I want to "pin" the distinct(for each day) somehow.
I know, this is a basic question.. first time user. :)
@denis krizanovic Depending on how the number is going to be used, you have several options. In your question, you mention 'each user', but your chart does not contain a dimension [username] so the count would be of all users. (I get the same error as Ayinde, so I cannot see the data, but I can see the fields you have on a chart).
I created a very simple data set of two fields: Name (with two values) and Date.
The simplest requirement would be displaying the information in a table with the month and name,
All the calculations used show the same correct value. The most basic calculation is a distinct count of a datetrunc calculation. The most complex is a FIXED LoD summing another Fixed LoD. The other two are single LoD, but one counts the [Name] and the other uses the basic CNTD aggregation. (Counting the date may work better than counting the user).
//C_CountD Day
COUNTD(DATETRUNC('day',[Date]))
//C_Monthly Fixed Aggregate CNTD
{ FIXED [Name],DATETRUNC('month',[Date]):[C_CountD Day]}
//C_Daily Fixed CNTD Name
{ FIXED [Name],DATETRUNC('day',[Date]):COUNTD([Name])}
//C_Monthly Fixed on Fixed
{ FIXED [Name],DATETRUNC('month',[Date]):SUM([C_Daily Fixed CNTD DateTrunc])}
You can start seeing differences in values when you change the dimensions in the view (e.g., change to date instead of month or remove names). Some of the calculation will definitely change values. Sometimes two calculations can be functionally equivalent, so they will always return the same value. What you want to happen when the view changes will determine which is the correct calculation to use.
The LoDs fix on both name and date level to keep events distinct. If Denis and Adam logged in at the exact same time. The FIXED name calculation will always view them as separate events. If name is not in the view, the non-FIXED name calculations would only see one event. This is why the first and third columns are different in the second table above.
