How to get the current date into the DATEDIFF formula?

[Martin_Barth]

Hello there,

for some calculations in the SAP Analytics Cloud I want to know the amount of days (or better weeks) between the January 1, 2023 (which is not s a date dimension in the model) and the current date within an import model consisting of SQL-data.

How can I do this within the SAP Analytics Cloud without having the need to rebuild the model?

Because: I found out that I could e.g. add a new field in the data warangling when creating a new model or rebuilding a model with the formula MAKEDATE (2023, 01,01). That would deliver me a new date dimension and I could calcuate then the date difference between this date produced via the MAKEDATE - formular and the current system date.

But in the past we had sometimes issues, when rebuilding a bigger more complex existing model in the SAC with the effect of crashing it.... And I do not want to risk this.

Unfortunately the MAKEDATE - formular obviously is only available in the data wrangling environment and not on story level...

So it seems as if my only possibility would be to calculate with the DATEDIFF-formular (or really rebuilding the model).

=> What I did not find out so far: Isn't there a value for the current date within in the DATEDIFF-formular?

I think of something like DATEDIFF ("2023-01-01", ???CURRENTDATE???, "Day") ... Is there a value I could paste in for ???CURRENTDATE??? within this formula?

Or are there any better ideas?

Thanks a lot and best regards,
Martin

[William_Yu]

If you just want to get number of weeks in advance formula you may just try this syntax:

WEEK(TODAY())

[Martin_Barth]

Hey William,

thanks a lot for your answer!

But your proposal did not work in the SAP Analytics Cloud. It told me, that the week - formula you mentioned is unknown.

So actually I solved the problem with a rebuild of the model inserting my reference date with the MAKEDATE - formula and the calculating the date difference with the DATEDIFFERENCE-formula with DAY-granularity and dividing this by 7 to get the amount of weeks and then rounding the number via CEIL-function:

CEIL((DATEDIFF([d/date_kpi] ,[d/Datum230101] ,"Day" )/7),0)