Forum Stats

  • 3,827,527 Users
  • 2,260,790 Discussions
  • 7,897,291 Comments

Discussions

AGO function - culculating dynamic OFFSET with TIMESTAMPDIFF

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

  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,570 Blue Diamond

    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 Member Posts: 3 Green Ribbon

    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

  • Christian Berg-0racle
    Christian Berg-0racle Everything Analytics And Data Member Posts: 9,639 Gold Crown

    @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 Member Posts: 3 Green Ribbon

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

  • Gianni Ceresa
    Gianni Ceresa Managing Director | Oracle ACE Director Member Posts: 6,570 Blue Diamond

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

  • Christian Berg-0racle
    Christian Berg-0racle Everything Analytics And Data Member Posts: 9,639 Gold Crown

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