14 Replies Latest reply: Jul 28, 2015 1:36 PM by Vince Agresto RSS

    Show minimum date greater than today....

    Vince Agresto

      Morning,

       

      I am trying to find a way to display milestones on a chart- the problem is that I have repetitively named milestones and I have to tell the chart which to display. Here are the characteristics of an if expression I want to create:

      • If there is only one milestone with a particular name show the date
      • If there are more than one milestone with a particular name and they all happen in the future (> today) show the minimum date
      • If there are more than one milestone with a particular name and they all happen in the past (< today) show the maximum date
      • If there are more than one milestone with a particular name and some happen in the past and some in the future show the next occurring date

       

      The data would look something like this if today = 7/28/15:

      MilestoneCalendarDate
      Start1/1/15
      Jump2/2/15
      Jump3/3/15
      Jump4/4/15
      Kick5/5/15
      Kick6/6/15
      Kick7/7/15
      Kick8/8/15
      Kick9/9/15
      Spin10/10/15
      Spin11/11/15
      Spin12/12/15
      Finish1/1/16

       

      The red highlights are the milestones I want to show in the chart:

       

      The dimension on the chart is - Milestone and

      I have started the following expression and everything is working so far, except the next (Spin in the example above), which I am trying to perform as set analysis in the last nest of the if statement:

      if (count ([Milestone])=1, CalendarDate

               if (count ([Milestone])>1 and min(CalendarDate)-today()>=0, min(CalendarDate),

                          if (count ([Milestone])>1 and max(CalendarDate)-today()<=0, max(CalendarDate),

                                 if (count ([Milestone])>1 and (min(CalendarDate)-today()>=0 and max(CalendarDate)-today()<=0),

                                 Only({<CalendarDate= {">=$(= today())"}>} CalendarDate)

                                 )

                          )

                )

      )

       

      Any help or guidance would be greatly appreciated.

       

      Thanks,
      Vince