I created this analysis in Sample Sales:
The formula for the Trend column is: TRENDLINE("Base Facts"."Revenue", ("Time"."Per Name Month"), 'LINEAR', 'VALUE')
It returns these results, with a gently sloping Trend line.
For some of the other views in this analysis, I also need the Per Name Year column, so I added it to the criteria. Now let's look at the results:
The values in the Trend column, and of course its corresponding green trend line in the graph, are no longer the same as they were a moment ago, before adding the Year column.
But why? Why does the formula return one set of results if Year is not in the criteria (picture 1), and a different set of results if Year is in the criteria (picture 2)? The number of rows doesn't change, the revenue column doesn't change, so why should the results of the TRENDLINE function change?
I can "fix" the formula by adding the Year component, and now I see the original values again:
TRENDLINE("Base Facts"."Revenue", ("Time"."Per Name Year","Time"."Per Name Month"), 'LINEAR', 'VALUE')
But again, why? Why is that necessary, and why/how did it change the results? My GUESS is that there is some type of smoothing technique being applied when the higher time level is included in the formula, but I can't find that in any documentation.
The documentation for the Trendline analytic function refers to this syntax:
TRENDLINE(<numeric_expr>, ([<series>]) BY ([<partitionBy>]), <model_type>, <result_type>, [<number_of_degrees>])
Looking for clues, I found a reference to the series parameter in this Oracle documentation (https://docs.oracle.com/middleware/bidv1221/desktop/BIDVD/BIDVD.pdf ) for the Data Visualization Desktop that states: "The numeric_expr represents the Y value for the trend and the series (time columns) represent the X value." It presents this formula format:
TRENDLINE(revenue, _**(calendar_year, calendar_quarter, calendar_month)**_ BY (product), 'LINEAR', 'VALUE')
That syntax matches with my second formula, where I added Year to the series parameter. But the documentation doesn't go far enough in explaining that series parameter, because it doesn't explain the very significant difference in results when more than one time level column is included in the analysis, nor does it explain why a reference to the additional time level column in the formula produces different results.
Can someone help me understand exactly what is happening here?