Syntax difference European vs. Rest of the World

Hi everyone,

I am trying to make the DATEDIFF formula work.

Looks like this

DATEDIFF([d/"Model":Date1].[p/DAY];[d/"Model":Date2].[p/DAY];) -> system says, it received two arguments but is missing the third one. Now i add "day", i.e. it now looks like this:

DATEDIFF([d/"Model":Date1].[p/DAY];[d/"Model":Date2].[p/DAY];"DAY") -> system says "formula could not be computed. Try rewriting the formula". I assume I need to replace " with something else. I am using an European version, anyone have a clue? (I already tried 'day' or ' day ' without success)

Thanks!

Answers

  • Hello Georg,


    here is an example that worked for my on a very simple model:


    DATEDIFF([d/Date_2] ,[d/Date_1] ,"Day" )


    In my case Date 1 and Date 2 are basically day based dimension in terms of granularity.

    You can in the formula editor always use the key combination CTRL+SPACE, then it will also show the available entries - including the options for the 3rd input.


    Regards

    Ingo Hilgefort, SAP

  • Georg  0Georg 0
    edited January 16

    Hello @Ingo Hilgefort,

    Thanks for your quick reply.

    Unfortunately,

    DATEDIFF([d/Date_2] ,[d/Date_1] ,"Day" )

    does not work at all. I get the message:

    Formula could not be computed: datediff([d/"Model":Upload_date].[p/DAY], [d/"Model":Maturity].[p/DAY], "Day") . Try rewriting the formula.

    It starts to work only if I use ; instead of , still cannot figure out what I have to put in for " at the end

    E.g. if I use:

    DATEDIFF([d/"Model":Upload_date].[p/DAY]; [d/"Model":Maturity].[p/DAY];)

    I at least get:

    Formula could not be computed: Function "DateDiff" expects 3 parameters, but receives 2. Try using the expected number of parameters. Try rewriting the formula.

    This is driving me nuts.. 🤯

  • By the way, if I do it like this:

    DATEDIFF([d/"Test DATEDIFF":Date2]; [d/"Test DATEDIFF":Date];"Day" )

    then system says I need to add properties (which I have done in all the examples above)

    Why do you not need the properties @Ingo Hilgefort ?

  • Hi Georg,


    my dimension is modeled as an actual Date, so the property shouldn't be needed then, unless you want to use a different granularity from the hierarchy.

    Regards

    Ingo Hilgefort, SAP

  • I solved it. The problem was, that the datediff formula works - at least for me - only in the formula editor when setting up a model, not if I want to create a calculated dimension or measure.


Sign In or Register to comment.