topic Re: Show 'previous' activity in QlikView

Show 'previous' activity

dickelsa — Wed, 05 Mar 2014 17:39:05 GMT

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

Re: Show 'previous' activity

Wed, 05 Mar 2014 18:52:59 GMT

Hi,

Quick question. How do you determine the order. Like Talking is 1, drinking is 2, eating is 3, etx..

Thanks

Re: Show 'previous' activity

dickelsa — Wed, 05 Mar 2014 18:56:23 GMT

Hello

I dont 'number' the activties, but if you want to number them autonumber(activtiy) as activitynr

greets

dick

Re: Re: Show 'previous' activity

Wed, 05 Mar 2014 19:05:48 GMT

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

Re: Show 'previous' activity

santharubban — Wed, 05 Mar 2014 19:19:10 GMT

You try Peek(Date) as pervious date

i think i will help u

Re: Show 'previous' activity

dickelsa — Wed, 05 Mar 2014 19:21:47 GMT

Problem is, my table is not a straight table but a crosstable in loadscript.

Re: Show 'previous' activity

Wed, 05 Mar 2014 19:30:47 GMT

Can you attach a sample source file.

Also why can't you make it a straight table and then apply this logic.