Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Complicated Set Analysis

Hi experts,

For one of my projects I've been asked to Calculate the Average Cycle Time of all the projects between two dates only if the project has commenced today.

Worded differently:

if (  Milestone 1: Trigger Event = Today / May 1, 2014,

           avg( Date of Milestone 2 - Date of Milestone 3)

I've attached a qlikview document with my thoughts, but have spent far too long struggling on this - simply frustrated by this point.

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Maybe like

avg({<Milestone = {'TriggerEvent'}>*<ActualDate = {'$(=today())'}>}

aggr((max({<Milestone={'Event2'}>} ActualDate) - max({<Milestone={'Event1'}>} ActualDate)),[Project])

)

View solution in original post

8 Replies
Highlighted
MVP
MVP

Maybe like

avg({<Milestone = {'TriggerEvent'}>*<ActualDate = {'$(=today())'}>}

aggr((max({<Milestone={'Event2'}>} ActualDate) - max({<Milestone={'Event1'}>} ActualDate)),[Project])

)

View solution in original post

Highlighted
Not applicable

Great! I find it unfair how I spend 3 hours on a problem and then it's solved immediately after posting it on here.   Thank you swuehl

Highlighted
MVP
MVP

avg(

  {$<Project = P({1<Milestone={TriggerEvent},ActualDate={'$(=today())'}>} Project)>}

  aggr((max({<Milestone={Event2}>} ActualDate) - max({<Milestone={Event1}>} ActualDate)),[Project])

)

Highlighted
Not applicable

swuehl -- what if I wanted to graph this, where as instead of today() I replace that with the dimension - I can't seem to get that to work either (*sigh*)

problem.png

I replaced today with (=$(Max(Date)) to try and say within each Dimension, plot all the Commencement date cycle times. Do you mind helping out with that if it's possible?

Highlighted
Creator II
Creator II

Isn't your = sign in the wrong place there? <ActualDate={'($(=Max(Date))'}>}

Highlighted
MVP
MVP

You can't make the set analysis responsive to the current dimension value, since set analysis is only evaluated once per chart, not per chart dimension value.

You would need some other approach. Since I can't see the Date field in your last sample app, please update your sample app with a more accurate data model.

Highlighted
Not applicable

=avg(

  {<Milestone = {'TriggerEvent'}> * <ActualDate = {'[DIMENSION ACTUAL DATE]'}>}

aggr((max({<Milestone={'Event2'}>} ActualDate) - max({<Milestone={'Event1'}>} ActualDate)),[Project])

)

Maybe the approach I'm taking is incorrect, but hopefully you understand the ask: Day over Day Cycle time for projects that finish each day.  Any idea on where to start in order to do that?  Would modifying the load script help?

Best regards,

Greg

Highlighted
MVP
MVP

Maybe like this?

If your ActualDate data values contain no blank values, but are NULL, I think this should be enough:

=avg(

  {<Milestone = {'TriggerEvent'}>}

aggr((max({<Milestone={'Event2'}>} ActualDate) - max({<Milestone={'Event1'}>} ActualDate)),[Project])

)

If you may have blank ActualDate Values, then you may need to filter these blanks:

=avg(

  {<Milestone = {'TriggerEvent'}>*<ActualDate = {"=len(ActualDate)"}> }

aggr((max({<Milestone={'Event2'}>} ActualDate) - max({<Milestone={'Event1'}>} ActualDate)),[Project])

)