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.

[girirajsinh]

Which one do you intend to show out of those two records for the Employee 2?

[jpbourse74]

I would do it like that. Greetings.

How do I add a QVW?

[sunny_talwar]

If this is what is needed, then I would suggest doing this rather instead of using Aggr(NODISTINCT)

Count(TOTAL <[Employee Name]> %_TransactionID)

[jpbourse74]

I add a QVW

[Anonymous]

The Transaction Expressions calculated for each row. so I have the number/amounts of transactions for each employee within each shift (clocked in session).

I believe part of the issues is the way in which the data is joining.  The transaction data in table A joins to clock data in table B on a key field however the key field for the 2 sets of clock times for employee 2 is the same.

I may have to look at the underlying tables and joins...

ie

[sunny_talwar · Accepted solution]

I think you are right.... you need to figure out if the join is correct or not....

[Anonymous]

just checked table joins in underlying database which confirms non-unique joins resulting in the duplicate result when employee has multiple shifts.

the calculation will therefore never be possible

[sunny_talwar]

It might be if you don't use ClockInDate (which is a timestamp) as your dimension, no?

[Anonymous]

No the table join means that the system sees each transaction against both of the clock in sessions.

The solution would be to have a time field against the transaction and then join transaction where the trans time is between the clock in date. This is easy in SQL but unfortunately Qlikview's pseudo SQL is not capable of complex joins (to the best of my Qlikview knowledge) nor I'm going to create the key that would do the join or code the clock time against each transaction as will over 20mm rows its not that important at this state.

Thank you all for your help