Difficulty with Linest_m combined with set analysis and aggr

[#1]

Hi all,

I have a problem with the LINEST_M function I can’t seem to figure out.

I have two objects: A line chart and a straight chart. In the line chart I have a Dimension YearMonth (which is of date-type and is basically the first day of the month of a transaction) and an expression Sum([Customs duty paid]) / Sum([Customs value]).

The straight chart has a double function: making a selection in the country of destination and showing some information. It has dimension [Country of destination], and the following expressions:

Sum({$<[Country of destination] = >} [Customs value]) //Hidden and just for sorting

=num(sum({$<[Country of destination]=>} [Customs duty paid]) / Sum({$<[Country of destination]=>} [Customs value]),'##.##%') //Applied duty rate percentage

=if(
LINEST_M({$<[Country of destination] = >} aggr(sum([Customs duty paid])/Sum([Customs value]), YearMonth),YearMonth)
> 0,'qmem://<bundled>/BuiltIn/arrow_n.png',
if(LINEST_M({$<[Country of destination] = >} aggr(sum([Customs duty paid])/Sum([Customs value]), YearMonth),YearMonth)
< 0,'qmem://<bundled>/BuiltIn/arrow_s.png',)) //Is supposed to show an up arrow if the trendline goes up and a down arrow if the trendline goes down.

I am trying to achieve the following:

Show the up-arrow if the trendline for the line-chart goes up for the specific country of destination, and a down-arrow if the trendline goes down for the specific country of destination. This should disregard any selections in [Country of destination], so the arrow should also show when that country has not in the current selection.

The only problem I (think I) am running into is with the LINEST_M({$<[Country of destination] = >} aggr(sum([Customs duty paid])/Sum([Customs value]), YearMonth),YearMonth) part of the expression. It is doing something, but I’m fairly sure it’s not doing exactly what it’s supposed to.

Can anyone help me with this?

Please also see the attached example.

[#2]

Anybody that can help here?

The problem is that the up/down arrows do not always correspond to the actual slope of the trendline or they even disappear when changing selections in YearMonth or [Country of destination], even if there is data available.

[#3 · Accepted solution]

I fixed it myself, such that the sign of the slope is generally correct. Changed the formula to:

linest_M({<[Country of destination] = >}aggr(sum({<[Country of destination] = >}[Customs duty paid]) / sum({<[Country of destination] = >}[Customs value]),YearMonth,[Country of destination]),YearMonth)

This still gives a different outcome for the slope then what QV shows at the trendline, but this is because qlikview internally ranks the YearMonth field when calculating the trendline, and we are not allowed to do this in the Linest_M function. So basically, the trendline itself is calculated with X-values {1,2,3,...} and the linest_m function calculates it with X-values along the line of {42000,42030,42061,...} because of how dates are treated.

So this solution is not perfect, but I believe it will do the job.