nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

AGO function on TIMESTAMPDIFF

choracy69

Hi,

I have a problem with the operation of the formula in the analysis.,

I'm trying to use the functions AGO on operations SUM (TIMESTAMPDIFF ())

In my analysis I have:

Formule for NOW column:

SUM(TIMESTAMPDIFF(SQL_TSI_DAY, "Purchase Details"."Due Date", "Purchase Details"."Need By Date"))

Formule for AGO column:

AGO(SUM(TIMESTAMPDIFF(SQL_TSI_DAY, "Purchase Details"."Due Date", "Purchase Details"."Need By Date")),"Time"."Time"."Year",1)

And I get results:

.My question: is it possible to make it work?

Find more posts tagged with

Accepted answers

All comments

choracy69

Someone has any suggestions?

[Deleted User]

Doesn't seem to work through Answers with that formula but works fine as an RPD column with derived calc.

http://slc02okq.oracle.com:7780/analytics/saw.dll?Answers&path=%2Fshared%2FOTN%2FTimestampdiff%20yago

(Prodney / Admin123)

choracy69

Hi Christian,

Thanks for reponse.

I analyzed your example, and in my opinion it falls short. This is your example:

In my opinion In 2012 row should be:

2012 || 993612 || 960672 || ...

Because the column YAGO should be the value of the previous year and are still the same.

[Deleted User]

choracy69 wrote:

Hi Christian,
Thanks for reponse.

I analyzed your example, and in my opinion it falls short.

Christian Berg wrote:

Doesn't seem to work through Answers

That's....precisely what I wrote. It does not seem to work through Answers. If you looked at the analysis you'd see that once it's with a formula like yours and once with a formula using a global variable.

choracy69

Ok, I understand.

That's how it implement the RPD?
How to perform a column in a fact: TIMESTAMPDIFF (SQL_TSI_DAY, data1, data2) wherein the two dates are the dimensions. And then the function AGO also reluctance to act.

[Deleted User]

Like any other calc and derived ago:

choracy69

Thanks for response.

Ok that I create column with timestampdiff and add agregation sum for it - it works.

But I have formule in answer something like:

sum(TIMESTAMPDIFF(SQL_TSI_DAY,."Invoice Date", ."DUE_DATE") * "Invoiced Amount" BY "Business Unit Name", "Supplier Name") / sum("Invoiced Amount" by ""Business Unit Name","Supplier Name")

How can I implement it in RPD?

I can't use agreagtion sum for TIMESTAMPDIFF(SQL_TSI_DAY,."Invoice Date", ."DUE_DATE") becasue I need sum of TIMESTAMPDIFF(SQL_TSI_DAY,."Invoice Date", ."DUE_DATE") and Invoiced Amount for each Business Unit/Supplier.

And when I use AGO function on sum(TIMESTAMPDIFF(SQL_TSI_DAY,."Invoice Date", ."DUE_DATE") * "Invoiced Amount" BY "Business Unit Name", "Supplier Name") - not work.

[Deleted User]

Why do you insist on using Answers?

Understand your formula. Split it up. Create appropriate columns in the RPD and then calculate.

"Invoiced Amount" BY "Business Unit Name" ... that's a level-based measure

"Invoiced Amount" by ""Business Unit Name","Supplier Name" ... that's another LBM

AGO is an AGO

etc.

The RPD is there to manage these things in a logical way rather than in one long formula sausage.