Oracle Business Intelligence

Products Banner

AGO function - culculating dynamic OFFSET with TIMESTAMPDIFF

Received Response
To solve the problem I thought of using the TIMESTAMPDIFF function in the offset but it doesn't work and I tried various solutions:

AGO ("SellOut Measurements (Detail)". "Sellout Volume", "Dimension: Time". "Time Hierarchy - Week Year". "Week", OFFSET)

1 attempt
TIMESTAMPDIFF(SQL_TSI_WEEK, timestamp '2022-03-28 00:00:00', "Dimension: Time". "Date")

2 tried
CAST(TIMESTAMPDIFF (SQL_TSI_WEEK, timestamp '2022-03-28 00:00:00', "Dimension: Time". "Date") AS INT)

3 attempt
CAST(TIMESTAMPDIFF (SQL_TSI_WEEK, timestamp '2022-03-28 00:00:00', "Dimension: Time". "Date") AS DOUBLE)

None of this worked, what am I doing wrong?
Many thanks in advance




  • None of this worked, what am I doing wrong?

    If you only enter these 3 TIMESTAMPDIFF code in a column formula, what do you get as result?

    And what does "none of this worked" means? You got an error? You got nothing? You got something different than what you expected?

  • First of all thanks for your reply Gianni.

    If I enter just the 3 formulas that I wrote above they worked, all of them. If I use one of those three formulas as offset item of the AGO function I got the following error message:

    Sintassi della formula non valida.

    [nQSError: 10058] Si è verificato un errore generico. [nQSError: 43113] Messaggio di errore restituito OBIS. [nQSError: 23024] L'ultimo argomento della funzione AGO deve essere un valore intero.Per ulteriori dettagli sull'errore, contattare l'amministratore del sistema per verificare il log. (HY000)

    Istruzione SQL eseguita: SELECT AGO("Misure SellOut (Dettaglio)"."Volume Sellout", "Dimensione: Tempo"."Gerarchia Tempo - Anno"."Giorno", TIMESTAMPDIFF(SQL_TSI_WEEK, timestamp '2022-03-28 00:00:00' , "Dimensione: Tempo"."Data"))/1000 FROM "PL Core Pricing"



  • @User_BO518 La lingua generale del forum è l'inglese, quindi nell'interesse di tutti gli utenti del forum:

    The important part of the error message is this:

    [nQSError: 23024] The last argument of the AGO function must be an integer value. For more details on the error, please contact your system administrator to check the log. (HY000)

    So that means whatever you put in last place in the formula does not yield you a proper numerical integer.

  • Ok, thanks a lot for your reply and sorry for the italian message

  • [nQSError: 23024] The last argument to the AGO function must be a constant integer. (HY000)

    This is the error I got in OAS 6.4 building a case similar to yours. As you can see the main difference in the message is "constant".

    The formula is expecting a value that doesn't come from a dynamic formula and therefore being different on every line and requiring to be calculated, it should be fixed and known.

    I believe this is because of the complex SQL logic the tool generate to find the value of the AGO function (jumping around the rowset to find the required offset etc.).

  • Cool translation - the italian error message doesn't say "constant". Facepalm