Hitachi Vantara Pentaho Community Forums
Results 1 to 4 of 4

Thread: Use formula to calculate end of month using year & month in string format

  1. #1
    Join Date
    Dec 2016
    Posts
    27

    Default Use formula to calculate end of month using year & month in string format

    Hi,

    I'm struggling to create a valid (end of month) date using year and month values (stored as strings). I get the error as attached ("I cannot convert the specified value to data type 0")

    I've been trying to use this formula.

    DATEVALUE(DATE([Source_Year]; [Source_Month1] + 1; 1) - 1)

    Can you suggest where I am going wrong?

    Thanks,

    Andy

    PS [Source Month 2], another field has some null values in it. So I will need to check for this using if(isblank). Can I use NULL() as a result if this isblank condition is met?

    Name:  pdi1.jpg
Views: 314
Size:  25.5 KBName:  pdi2.jpg
Views: 298
Size:  23.6 KB

  2. #2
    Join Date
    Nov 2009
    Posts
    688

    Default

    Your formula is correct. But did you set the value type to "Date" in the formula step?

  3. #3
    Join Date
    Jun 2012
    Posts
    5,534

    Default

    How should the formula parser know that -1 is meant to be a day?

    Name:  217555.jpg
Views: 291
Size:  17.4 KB
    So long, and thanks for all the fish.

  4. #4
    Join Date
    Dec 2016
    Posts
    27

    Default

    Hi both,

    Johan - I had failed to set type to date - thanks for pointing that out. Also Marabu you're right of course, and I have made that change

    Thanks,


    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.