Bar chart : trend line based on data of previous years only

[Gaël]

Hi everyone,
I have this bar chart of the number of requests per quarter year.
There is a clear ascending linear trend that I want to visualize with a trend line. The "naive" built-in trendline from Qlik Sense is biased by the incomplete data of the current, incomplete quarter year as shown in the figure. The reasonable thing to do is to base that trend line on the years before YearStart(Today()).
I guess this is a pretty common thing, but I can't figure out how to implement this. Do you have a hint?

What I tried so far:

• Checking similar topics here and there.
• To calculate the intercept and slope of the regression line, I use Linest_B() and Linest_M().
• As X values, I use numeric value of the quarter year: (Num#(Left([Reception YearQuarter],4))+(Num#(Mid([Reception YearQuarter],7,1))-1)/4))
• As Y values, I use Aggr(Count(RequestID), [Reception YearQuarter]) over the set of Reception Dates from previous years {1<[Reception Date]={"<$(=Date(YearStart(Floor(Today()))))"}>}, or as a complete expression: Aggr(Count({1<[Reception Date]={"<$(=Date(YearStart(Floor(Today()))))"}>} RequestID), [Reception YearQuarter]).
• In a table showing Reception Year-Quarter (both text and numeric) and Count(RequestID), I succeed in correctly calculating Linest_B(Y values, X values) and Linest_M(...) as additional measures, but not Linest_B(...) + Linest_M(...)*X-value
• As a reference line this does not work either.

AggregatedSampleData:
LOAD * INLINE [
Reception Year-Quarter, Count
2021-Q1, 17871
2021-Q2, 17515
2021-Q3, 16443
2021-Q4, 19041
2022-Q1, 19297
2022-Q2, 18020
2022-Q3, 16881
2022-Q4, 19582
2023-Q1, 21232
2023-Q2, 19139
2023-Q3, 18494
2023-Q4, 21002
2024-Q1, 21784
2024-Q2, 1790

];

[Anil_Babu_Samineni]

Please share sample QVF file to test and explain the outcome. 

[Gaël]

With my limited rights, it seems I'm not able to export a QVF file.
In the following test data, there's an increase of +1 request per quarter year from one year to the next (total: +4 per year), with within-year variation. The data for the current year is incomplete and results in a wrong linear trend line in the bar chart of Count(RequestID) by [Reception-YearQuarter].

SampleData:
LOAD
RequestID,
ReceptionDate,
Dual(Year(ReceptionDate)&'-Q'&Num(Ceil(Num(Month(ReceptionDate))/3)),QuarterStart(ReceptionDate)) AS [Reception-YearQuarter]
;
LOAD
RequestID,
Date(Date#([Date], 'YYYY-MM-DD') ) AS ReceptionDate,
;
LOAD * INLINE [
RequestID, Date
9, 2022-01-01
10, 2022-01-01
11, 2022-04-01
12, 2022-04-01
13, 2022-04-01
14, 2022-07-01
15, 2022-07-01
16, 2022-07-01
17, 2022-07-01
18, 2022-10-01
19, 2022-10-01
20, 2022-10-01
21, 2023-01-01
22, 2023-01-01
23, 2023-01-01
24, 2023-04-01
25, 2023-04-01
26, 2023-04-01
27, 2023-04-01
28, 2023-07-01
29, 2023-07-01
30, 2023-07-01
31, 2023-07-01
32, 2023-07-01
33, 2023-10-01
34, 2023-10-01
35, 2023-10-01
36, 2023-10-01
37, 2024-01-01
38, 2024-01-01
39, 2024-01-01
40, 2024-01-01
41, 2024-04-01

];

[Gaël · Accepted solution]

If anyone is interested: I found the following solution:

1. create a copy of the measure to be displayed.
   In my example: count(OrderID)
2. name the copy "current year", and limit the data accordingly.
   In my example: Count({$<[Reception Year]={"$(=Max([Reception Year]))"}>} OrderID)
3. name the original "previous years", limit the data accordingly and additionally make sure that data for the current year are NULL instead of zero. This is to ensure the linear regression does not include those zero values.
   In my example: if(Count({$<[Reception Year]={"<$(=Max([Reception Year]))"}>} OrderID)>0, Count({$<[Reception Year]={"<$(=Max([Reception Year]))"}>} OrderID), NULL)
4. add a linear trend line for "previous years". Voilà!
   

[Anil_Babu_Samineni]

I have marked your answer as accepted solution.