Hello folks.
I'm currently developing a dashboard for profiling network services. There's a very large (hundred of millions rows) log table in SQL from wich I extract and load into QlikView a smaller aggregated table with just three fields: The name of the service, the month and the average response time in miliseconds. Something like this:
ServicePerformance:
LOAD * INLINE
[
ServiceID, Month, AVGExecTime
'Service A', 1, 2315
'Service A', 2, 4152
'Service A', 3, 3221
'Service A', 4, 1572
'Service A', 5, 1782
'Service A', 6, 3265
'Service B', 1, 1542
'Service B', 2, 4351
'Service B', 3, 1098
'Service B', 4, 1509
'Service B', 5, 3265
'Service B', 6, 2517
'Service B', 7, 2210
'Service B', 8, 4500
];
The problem is I need to calculate trends over time but can't figure out the correct LINEST_M(syntax). I've tried:
Trends:
NOCONCATENATE LOAD
ServiceID,
Month,
LINEST_M(AVG(AVGExecTime), Month) as Trend
RESIDENT ServicePerformance
GROUP BY ServiceID, Month;
But this fails with: "Error in expression:Nested aggregation not allowed"
If I remove the aggregation:
Trends:
NOCONCATENATE LOAD
ServiceID,
Month,
LINEST_M(AVGExecTime, Month) as Trend
RESIDENT ServicePerformance
GROUP BY ServiceID, Month;
then there's no error but nothing is calculated and the field is empty.
I need this to get calculated in the script at load time, not in graphs/tables, etc.
Any help will be greatly appreciated.
Regards,
Ernesto.
