Forum Discussion

tobiasmcbride's avatar
tobiasmcbride
Helper III
6 years ago

Number at a particular date

Hi,   I have the following data on headcount.   Name Position Start Date End Date Current Status Joe Bloggsd Head of Marketing 21/02/2017   Established Noah Anyone Head of Opera...
Need Help
tobiasmcbride's avatar
tobiasmcbride
Helper III
6 years ago

Thanks for that however is saying that there's an expression error upon saving - OLE DB or ODBC error (we cannot convert the value null to type Date) - think it's because some of the end dates have 'null' in them so how is it best to fix this Mariusz ?

Mariusz's avatar
Mariusz
Community Champion
6 years ago

Hi tobiasmcbride 

 

Sorry for the late reply.

 

The function that is used in the attached file is converting nulls at end date to Today's date, or else you have nulls in the start date field ( this would be more likely a data quality issue ), it should work fine.

 

Are you connecting to the sql server?

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

 

  • tobiasmcbride's avatar
    tobiasmcbride
    Helper III
    6 years ago

    Hi Mariusz 

     

    Thanks for that. I do have some employees in this database with a null start date you are correct - these will be roles as yet not recruited for (i.e. we have a role outstanding but nobody yet confirmed for them and as such they have an undefined start date). Is there a way of altering that function to account for these people who have this undefined start date?

     

     

    • Mariusz's avatar
      Mariusz
      Community Champion
      6 years ago

      Hi tobiasmcbride 

       

      The easiest way would be to filter out these people ( start date <> null ) in a step before you invoke the function, as they have not had any working days there is no need for them to be in this table.

       

      Please see the adjusted file on the attached.

       

      Best Regards,
      Mariusz

      If this post helps, then please consider Accepting it as the solution.

      Please feel free to connect with me.
      LinkedIn

       

       

      • tobiasmcbride's avatar
        tobiasmcbride
        Helper III
        6 years ago

        Hi Mariusz ,

         

        Looking at data again these are budgeted leavers (i.e. those we budget to leave this coming FY) this is what we have in the database with no start date but a particulary end date given our attrition modelling. What would you suggest to do as would actually like to keep these budgeted leavers in there but work out a way to tweak the formula to take account of null dates?