topic Re: Forecasting within load script in QlikView

Forecasting within load script

didierodayo — Fri, 25 Nov 2016 08:55:49 GMT

Hello,

I have this complex project and need some help. I have a dashboard with several objects mostly based on trend analysis.

the dashboard is slow because most objects are based on agg function. I am trying to move some of the codes to the load script.

If(Service Type]='design' ,[Limit] -
LINEST_B({$<Date=>} Aggr(Sum({$<Date=>} [Metric Value]), Date,[Pipe Type],[Interface Type],Metric), Date))
/ LINEST_M({$<Date=>} Aggr(Sum({$<Date=>} [Metric Value]), Date,[Pipe Type],[Interface Type],Metric), Date)), Date) >= $(Numberofmonths)

Note ;

The chart displays the date when specific pipes will hit their limit

Dimensions on the Straight table are: ,[Pipe Type],[Interface Type],Metric

$(Numberofmonths) is a variable currently that allows for the selection of 3 ;6;12 months to see those that will hit the limit during that period.

Question:

is it possible to write a similar query /scenario in the load script because the above calculation is running on a large amount of data ?

any other suggestions or ideas?

Regards

Re: Forecasting within load script

Gysbert_Wassenaar — Fri, 25 Nov 2016 09:11:20 GMT

If you want to calculate things in the script then you first need to create a table that calculates the sum of [Metric Value] aggregated over the fields Date, [Pipe Type], [Interface Type] and Metric. You can add a where clause to filter on [Service Type] = design. Once you can that table you can create a new table to calculate the Linest_B and Linest_M and the division of the two over the sums of Metric over the Date field.

Btw. that expression you posted cannot work. The brackets and parentheses don't match up. I also don't understand how the >= $(Numberofmonths) fits with the rest of the expression

Re: Forecasting within load script

didierodayo — Fri, 25 Nov 2016 09:24:59 GMT

Hi Gysbert,

the actual script is below.

1- the dimensions are ,[Node Name],[Interface Name],Metric

2- [Metric Value $(vL.MetricValue)] is because there are calculated fields in the data such as [Metric Value avg] and [Metric Value max] that the user can select in the front end.

3- the expression currently works but really slow.

when you said to aggregate in the load script you mean Group By?

could you explain how to perform the Linest_B and Linest_M and the division in the load script?

Thanks

Re: Forecasting within load script

Gysbert_Wassenaar — Fri, 25 Nov 2016 09:50:41 GMT

when you said to aggregate in the load script you mean Group By?

Yes. And the linest functions are just aggregation functions too just like sum so you use a group by for those too. You said your chart dimensions are [Pipe Type],[Interface Type],Metric. You need to aggregate over those fields.