Forum Discussion

RonaldvdH's avatar
RonaldvdH
Post Patron
2 years ago

Function Related doesn't seem to work

Hey guys,   Im working on a formula that calculates the amount of hours between 2 dates based on working days. I've been wachting a clip on how to do that and i'm using this formula   Doorloopt...
Need Help
lbendlin's avatar
lbendlin
Super User
2 years ago

see attached. Can be further simplified if you want to avoid creating the MinuteCalendar table.  There will be a performance penalty for large date ranges.

 

 

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    The date table has nothing to do with your scenario.  You need a Minutes table, but my last version removed that requirement too.

  • lbendlin's avatar
    lbendlin
    Super User
    2 years ago

    As I said before, a MinuteCalendar is not required any more. Only the intervals are considered (cardinality reduction), so that will be faster.  But you can try materializing that table. There's no good reason to recreate it for every row (other than that the code looks slicker).

  • RonaldvdH's avatar
    RonaldvdH
    Post Patron
    2 years ago

    lbendlin I'm not sure your solution will work because I have a 1 to many relationship between the date Table and Incident table.

  • RonaldvdH's avatar
    RonaldvdH
    Post Patron
    2 years ago

    lbendlin in your formula there is a part : GeneratesSeries(0,1439)

    However seeing that my data has 2 years worth of dates and if I make a MinuteCalendar that table would have 1.051.200 rows I don't think that does wonders for the performance right ?

     

    Ive already tried change the value tot a 1.051.200 but my laptop is still busy .... hhaha