Problem with a box plot - or redesign is the issue?

[josephinetedesc]

Hi all

I have created a data model (hint to self:  in future ALWAYS create a data model first)

The data comes from my sql query like this:

ActivityID	Activity	Act_DtTm	Person
2296131	Activity2	22/01/2016	Georgey
2287402	Activity2	19/01/2016	Georgey
2303296	Activity1	28/01/2016	GeorgRingo
2282637	Activity2	15/01/2016	John
2285235	Activity1	18/01/2016	John
2292497	Activity2	21/01/2016	JohnPaul
2304394	Activity2	28/01/2016	JohnRingo
2296170	Activity1	22/01/2016	Johny
2304488	Activity1	28/01/2016	Paul
2304454	Activity2	28/01/2016	Paul
2276628	Activity2	13/01/2016	Pauly
2292481	Activity2	21/01/2016	Ringo
2300131	Activity2	25/01/2016	Ringoy

Ideally I want to get the data like this:

Person	Activity2	Activity1	(Activity2 Act_DtTm) - (Activity1Act_DtTm)    OR planTurn
Georgey	2		do not use
GeorgRingo		1	do not use
John	1	1	2.932233796
JohnPaul	1		do not use
JohnRingo	1		do not use
Johny		1	do not use
Paul	1	1	-0.007048611
Pauly	1		do not use
Ringo	1		do not use
Ringoy	1		do not use

If a person has Activity1 and an Activity2 I want to subtract the timestamps from each other - this is planTurn

This script works well at the moment - it gives me the values expected - i could let the 'na'  be null:

if(((Activity2 Act_DtTm)) -  (Activity1Act_DtTm)) >=-1, (((Activity2 Act_DtTm)) - ( (Activity1Act_DtTm))) ,'na')

However - when I want to do a Box Plot I need an expression -   My current expression will not work.

I have tried

a.     adding another sql script which will output Person, Activity, DateTime

b.     using the formula

if(Person= Previous(Person), Activity2Act_DtTm - previous(Activity1Act_DtTm)) as planTurn

However planTurn using this way does not always match what using the formula will give me.

So I would prefer to use the formula - but the values appear to be hard coded - they do do not change with the calculated planTun (that is calculated within the Qlikview script ???)  And I cannot show the legend.

I think the problem might be that I need to go back to the design.

Jo

[swuehl]

I am not sure I understand your issue.

Could you upload some sample data (e.g. in an excel file) and what you expect to see?

[josephinetedesc]

I think I have solved it!  One problem was bad design.  In old database speak I had an ID and the description rather just the ID as the link between 2 tables.

The other problem though is that it is hard to use a box plot graph unless you have a single dimension rather than a calculated/expression.  I am still working on that.

Jo