Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
activity | date |
talking | 1-1-2013 |
drinking | 20-1-2013 |
eating | 22-2-2013 |
running | 12-5-2014 |
sleeping | 2-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
Hi,
Quick question. How do you determine the order. Like Talking is 1, drinking is 2, eating is 3, etx..
Thanks
AJ
Hello
I dont 'number' the activties, but if you want to number them autonumber(activtiy) as activitynr
greets
dick
I created a table with StartDate and EndDate as per your logic. So now you can get your results based on which ever date you want to use.
Hope it helps
Thanks
AJ
You try Peek(Date) as pervious date
i think i will help u
Problem is, my table is not a straight table but a crosstable in loadscript.
Can you attach a sample source file.
Also why can't you make it a straight table and then apply this logic.