6 Replies Latest reply: Mar 5, 2014 2:30 PM by Ajay Krishnan Prabhakaran RSS

    Show 'previous' activity

    dick elsman

      Hello all,

       

      I am dealing with an issue at the moment.

      currently I am building a dashboard that should function as a timeline. with this I mean: I have a few activities that are linked to a startdate. the startdate from the second activity however, is the enddate of the first activity.

       

      the following example should explain it:

      given is the following table:

      activitydate
      talking1-1-2013
      drinking20-1-2013
      eating22-2-2013
      running12-5-2014
      sleeping2-6-2014

      now the activity 'Talking' starts at 1-1-2013 and ends at 20-1-2013, because then the activity 'Drinking' starts. The activity 'Drinking' ends on 22-2-2013 because then the activity 'eating' starts and so on.

       

      so when I should click the date 15-1-2013, the activity 'talking' should be activated, and so on

       

      i made this working with the following script:

       

      Activity1:

      NoConcatenate Load DISTINCT Date,

      Month(Date) as CalendarMonth,

      Day(Date) as CalendarDay,

      Week(Date) as CalendarWeek,

      Year(Date) as CalendarYear,

      IF (Ceil(num(Month(Date)))/3 > 0, 'Q' & Ceil(num(Month(Date))/3), null()) AS CalendarQuarter,

      If( IsNull( Activiteit ), Peek( Activiteit ), Activiteit ) as Activiteit,

      If( IsNull(Afdeling), Peek(Afdeling),Afdeling) as Afdeling,

      If( IsNull(Rij), Peek(Rij),Rij) as Rij,

      If( IsNull(Soort), Peek(Soort),Soort) as Soort

      Resident TempFactTable1

      Where Date<>''

      Order By Date ; // so that above values can be propagated downwards

       

      now i have the range of the dates linked to my activities.

       

      now comes the tricky part:

      I want to get the 'previous' activity with the startdate.

      How do i do this?

       

      I currently have this, but I cant figure out what to do.

      Can you guys help me?

       

      Thanks in advance,

       

      Dick