Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
consenit
Partner - Creator II
Partner - Creator II

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
Partner - Creator II
Partner - Creator II
Author


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;

View solution in original post

1 Reply
consenit
Partner - Creator II
Partner - Creator II
Author


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;