Visokio website     Downloads     Video tutorials     KnowledgeBase  
YTD Calculation - Visokio Forums
YTD Calculation
  •     rajeshbalu29 November 1, 2011 5:49AM
    Hi, I am working on a report for which I need to identify Year to date values as a formulated column.
    Can any one help me on the same.
    Eg: =IF(Field (Start_date) >= "1/1/2011"),"Yes",NULL)
  • 8 Comments
  •     rajeshbalu29 November 1, 2011 5:50AM
    Basically I am looking for a finding YTD dates from another date field.
  •     tjbate November 1, 2011 7:25AM
    Rajesh - To clarify, you want to populate a formula field as "Yes" or "No"/null based on relative values in a Date/Time field with a range of dates. If the value in the date range field is over a threshold, or the maximum value in the field, then "YES" else NO/null?
  •     rajeshbalu29 November 1, 2011 8:45AM
    Yes thats correct.
  • Guy_Cuthbert        Guy_Cuthbert November 1, 2011 12:41PM
    Rajesh

    It looks like you have one too many brackets in your formula (before the comma immediately before the "Yes"), and you need to convert your text ("1/1/2011") into a date first:

    IF([Start_date] >= TEXTTODATE("1/1/2011"),"Yes",NULL)

    That should work.
    Atheon Analytics Ltd
    w: www.atheonanalytics.com
    e: guy.cuthbert@atheon.co.uk
    t: +44 8444 145501
    m: +44 7973 550528
    s: guycuthbert
  •     rajeshbalu29 November 2, 2011 2:10AM
    Thanks and it works perfectly, but can we have a dynamic value rather having a static one like CurrentYear?
  •     steve November 2, 2011 3:35AM
    Do you mean the NOW function?

    IF([Start_date] >= NOW,"Yes",NULL)

    (today)

    or:

    IF([Start_date] >= TEXTTODATE(DATETOTEXT(NOW, "yyyy"), "yyyy"),"Yes",NULL)

    (this year)

    (untested - I think these are correct)
  •     rajeshbalu29 November 2, 2011 4:48AM
    This is perfect... Thanks...
  •     steve November 4, 2011 5:02AM
    For those with access to 2.7, last night's build has a new DATE function, so the above formula...
    IF([Start_date] >= TEXTTODATE(DATETOTEXT(NOW, "yyyy"), "yyyy"),"Yes",NULL)
    ... can be simplified as:
    IF([Start_date] >= DATE(DATEUNIT(NOW, "year")),"Yes",NULL)

Welcome!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Apply for Membership