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

josephinetedesc — Fri, 29 Jan 2016 05:18:36 GMT

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.

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

swuehl — Fri, 29 Jan 2016 11:29:07 GMT

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?

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

josephinetedesc — Sat, 30 Jan 2016 10:35:23 GMT

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.

topic Re: Problem with a box plot - or redesign is the issue? in QlikView

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

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

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