Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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