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:


      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:



      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,