Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

AGO function - culculating dynamic OFFSET with TIMESTAMPDIFF

Received Response
64
Views
6
Comments
User_BO518
User_BO518 Rank 1 - Community Starter
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

Federico


Tagged:

Answers

  • 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?

  • User_BO518
    User_BO518 Rank 1 - Community Starter

    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"


    Thanks

    Federico

  • [Deleted User]
    [Deleted User] Rank 10 - Analytics Guru

    @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.

  • User_BO518
    User_BO518 Rank 1 - Community Starter

    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.).

  • [Deleted User]
    [Deleted User] Rank 10 - Analytics Guru

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