Oracle AI and Analytics

PeteSiekierski

I've been trying to add a trend line to my line graph in OAC, and it's driving me bonkers.

The graph itself is pretty simple. The X-axis consists of days, one day at a time, that comes from a "load date" on each record. The Y-axis is a count of all the contracts in progress per a given load date. OAC easily constructs a line graph for me to show numbers of contracts in process over time, like this:

I'd like to add a trend line to show the gradual reduction in the number of contracts in process, but here, I'm stumped. This formula passes muster for syntax when I define it on the Criteria tab, but gives me the "There is inconsistency between Trendline grain and its Y-axis grain" error every time I try to look at my Results.

TRENDLINE(COUNT("Fact - Snowflake Contract History"."CONTRACT ID"), (CAST("Fact - Snowflake Contract History"."LOAD DATE" AS DATE)) BY (), 'LINEAR', 'VALUE')

I imagine I could probably insert this column's value into a scale marker to get it to overlay onto the graph, but I can't even get as far as looking at my Results to do that.

Clare Murtagh

Hi @PeteSiekierski

The X value inside your TRENDLINE has to match the column on your graph's X axis exactly. You've got CAST(LOAD DATE AS DATE) in the formula, but I'm guessing your criteria just has plain LOAD DATE, so OAC sees two different grains and grumbles.

1. Make them identical. Either put the same CAST on your LOAD DATE column in the criteria, or take it out of the trendline.
2. Drop the empty BY (), you don't need it:
   TRENDLINE(COUNT("Fact - Snowflake Contract History"."CONTRACT ID"), (CAST("Fact - Snowflake Contract History"."LOAD DATE" AS DATE)), 'LINEAR', 'VALUE')
3. Check for any extra columns in the criteria (hidden ones too), as they change the grain.
4. Once it runs, just pop the trendline column on the Y axis next to your count. No scale marker needed.

Syntax is in the docs under Visualizing Data and Building Reports in Oracle Analytics Cloud > Analytics Functions: https://docs.oracle.com/en/cloud/paas/analytics-cloud/acubi/analytics-functions.html

Same page also says Oracle recommends using Add Statistics on a visualization instead, so try and see if you can rebuild it as a workbook. Hope that helps!