2 Replies Latest reply: Jan 30, 2016 5:35 AM by Jo Tedesco RSS

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

    Jo Tedesco

      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