AGGR() with calculated Time field

[Anonymous]

Hi,

I'm have real problems trying to Aggr results when I add a time element.

Below is a sample table;

3 fields need to be calculated using Aggr (i believe) as Employee 2 appears twice the results of the 3 transaction fields for employee 2 are duplicated using a standard Sum/Count expression.  If I use an Aggr expression then I get the results as seen above.

If I add a 2nd aggr level by adding ClockInDate to therefore make each row unique I get No result just blank for all rows.

# Transactions Handled

=concat(Aggr(Count(%_TransactionID),[Employee Name]))

Net of Transactions Handled

=Sum(Aggr(sum([Net Sales Before Discount]),[Employee Name]))

Avg Net Per Transaction

=Sum(Aggr(sum([Net Sales Before Discount])/Count(%_TransactionID),[Employee Name]))

ClockInDate	ClockOutDate
20/01/2017 06:45:00	20/01/2017 16:13:00
20/01/2017 10:00:00	20/01/2017 13:57:00
20/01/2017 16:02:00	20/01/2017 23:39:00
20/01/2017 17:24:00	20/01/2017 23:37:00

ClockInDate and ClockOutDate are datetime fields and Time(ClockInDate ,'hh:mm:ss') is used for the results table at the top Shift Length is calculated as Time([ClockOutDate]-[ClockInDate],'hh:mm:ss')

If any of these Time expressions are used in the Aggr the Aggr does not work.

[sunny_talwar]

Are you using  Clock In Date, Clock Out Date and Shift Length as dimensions or expressions? Also is Transaction date a date field or a time field? The reason I ask this is because, you need unique dimension to remove duplicate Employee Name. Clock In Date, Clock Out Date and Shift Length are def. not unique and will lead to seeing multiple rows if used dimensions

[jpbourse74]

With the column Clock in Date and Clock out Date, it is not possible. Option 1: If you can delete these two columns and leave the sum of the times between Clock in Date and Clock out date per employee. Option 2: Remove the two previous columns and make the sum or count without the aggr. Then if I want to know every employee of what hour at what time I work I enter the employee and add a size of groups. I hope it is useful. regards

[Anonymous]

Hi Sunny,

Yes Time(ClockInDate,'hh:mm:ss') and Time(ClockOutDate,'hh:mm:ss') are used as calculated dimensions.

The date part (which is removed is the same as shown for TransactionDate but the time element is different, this puzzled me as to why its not seeing the time as unique.

[Anonymous]

Hi Juan,

Really need to have both Clock times as part of control reporting.

[sunny_talwar]

May be use them as expressions instead of dimensions because they are different for the two rows of Employee and your dimension would show them in two different rows?

But even as an expression, how do you plan to show these times? Sum them? Avg them? Min or Max?

[girirajsinh]

Hi John

Try following for

# Transactions Handled

=concat(Aggr(Count(%_TransactionID),[Employee Name],ClockOutDate,ClockInDate))

disable other two expressions for now and check what you get from above expression

[Anonymous]

Hi Girirajsinh,

This is one of the combinations I tried previously.  Applied just this expression with all other expressions disabled, all other fields (first 5) are dimensions.

Employee Name	Transaction Date	Clock In Date	Clock Out Date	Shift Length	concat(Aggr(Count(%_TransactionID),[Employee Name],ClockOutDate,ClockInDate))
Employee 1	20/01/2017	06:45:00	16:13:00	09:28:00	67
Employee 2	20/01/2017	10:00:00	13:57:00	03:57:00	177
Employee 2	20/01/2017	17:24:00	23:37:00	06:13:00	177
Employee 3	20/01/2017	16:02:00	23:39:00	07:37:00	215

[sunny_talwar]

What is the output you are looking to get? Just one row for each Employee Name?

[vinieme12]

Try

Aggr(NODISTINCT Count(%_TransactionID),[Employee Name])