topic Re: Complicated Set Analysis in QlikView

Complicated Set Analysis

Thu, 01 May 2014 12:32:44 GMT

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.

Re: Complicated Set Analysis

swuehl — Thu, 01 May 2014 12:55:23 GMT

Maybe like

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

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

)

Re: Complicated Set Analysis

Thu, 01 May 2014 13:08:23 GMT

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

Re: Complicated Set Analysis

maxgro — Thu, 01 May 2014 13:29:39 GMT

avg(

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

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

)

Re: Complicated Set Analysis

Thu, 01 May 2014 13:44:16 GMT

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?

Re: Complicated Set Analysis

richnorris — Thu, 01 May 2014 13:50:26 GMT

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

Re: Complicated Set Analysis

swuehl — Thu, 01 May 2014 13:58:42 GMT

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.

Re: Re: Complicated Set Analysis

Thu, 01 May 2014 14:23:04 GMT

=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

Re: Re: Re: Complicated Set Analysis

swuehl — Thu, 01 May 2014 17:03:07 GMT

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

)