Complicated Set Analysis

[#1]

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.

[swuehl · Accepted solution]

Maybe like

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

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

)

[#3]

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

[maxgro]

avg(

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

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

)

[#5]

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*)

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?

[richnorris]

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

[swuehl]

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.

[#8]

=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

[swuehl]

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])

)