AGO function - culculating dynamic OFFSET with TIMESTAMPDIFF

Question

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

[Deleted User] · Answer

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

Gianni Ceresa · Answer

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

User_BO518 · Answer

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