Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

consenit
Not applicable

Need help with LINEST_M() syntax in script.

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.

1 Solution

Accepted Solutions
consenit
Not applicable

Re: Need help with LINEST_M() syntax in script.


Solved it. Just using the ServideID dimension for the grouping with INNER JOIN LOAD the main table did the trick:

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, 1565
'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
]
;

INNER JOIN LOAD
ServiceID,
LINEST_M(AVGExecTime, Month) as Trend
RESIDENT ServicePerformance
GROUP BY ServiceID;

1 Reply
consenit
Not applicable

Re: Need help with LINEST_M() syntax in script.


Solved it. Just using the ServideID dimension for the grouping with INNER JOIN LOAD the main table did the trick:

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, 1565
'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
]
;

INNER JOIN LOAD
ServiceID,
LINEST_M(AVGExecTime, Month) as Trend
RESIDENT ServicePerformance
GROUP BY ServiceID;