Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
josephinetedesc
Contributor III

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

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:

    

ActivityIDActivityAct_DtTmPerson
2296131Activity222/01/2016Georgey
2287402Activity219/01/2016Georgey
2303296Activity128/01/2016GeorgRingo
2282637Activity215/01/2016John
2285235Activity118/01/2016John
2292497Activity221/01/2016JohnPaul
2304394Activity228/01/2016JohnRingo
2296170Activity122/01/2016Johny
2304488Activity128/01/2016Paul
2304454Activity228/01/2016Paul
2276628Activity213/01/2016Pauly
2292481Activity221/01/2016Ringo
2300131Activity225/01/2016Ringoy

Ideally I want to get the data like this:

    

PersonActivity2Activity1(Activity2 Act_DtTm) - (Activity1Act_DtTm)    OR planTurn
Georgey2 do not use
GeorgRingo 1do not use
John112.932233796
JohnPaul1 do not use
JohnRingo1 do not use
Johny 1do not use
Paul11-0.007048611
Pauly1 do not use
Ringo1 do not use
Ringoy1 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.

Capture.PNG

Capture1.PNG

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

Jo

Tags (1)
2 Replies
MVP
MVP

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

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
Contributor III

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

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

Community Browser