Overview of results for a particular item

[Dayna]

Dear all,

I hope you can assist, I think this is an easy one but not sure the best method to achieve my requirements..

I have many projects, each have a list of tasks associated to them with a yes/no marked agaisnt. What I want to do, is when all the tasks have been completed mark the project as complete, if some have been done, then i would like to show the project as in progress and the percentage complete, and if none of the tasks have been done - so it as none started.

Could someone please help me?

Kind Regards,
Dayna

[jonathandienst]

Dayna

If you set this up as a straight table dimensioned by project you could use the expressions:

• count(distinct task) -- count of tasks for the project
• count(distinct if(taskstatus = 'complete', task)) -- count of completed tasks
• if(aggr(count(distinct if(taskstatus = 'complete', task)), project) = 0, 'Not started') -- not started indication
• .... and so on

(you may need to correct for closing brackets)

Hope that helps

Jonathan

[johnw · Accepted solution]

Putting it together into one expression:

pick(1+match(count({<taskstatus={'complete'}>} distinct task),0,count(distinct task))
, num(count({<taskstatus={'complete'}>} distinct task) / count(distinct task),'#,##0%')
,'none started'
,'complete')

See attached.

If you don't want it to be responsive to user selections, it would be more efficient to do it in the script, but I've assumed you want the project status to change depending on what is selected.

[Dayna]

John,

As always, you are a star!! Thank you soooo much!

Kind Regards,
Dayna

[Dayna]

Sorry - how would I put that in the script?

[johnw]

Well, it's similar to the expression above, but you can't use set analysis in the script. So it needs to be converted to a count(if()) syntax. And then we need a group by project to be able to count everything by project. Like this:

Projects:
LOAD
project
,pick(1+match(count(distinct if(taskstatus='complete', task)),0,count(distinct task))
, num(count(distinct if(taskstatus='complete', task)) / count(distinct task),'#,##0%')
,'none started'
,'complete') as projectstatus
RESIDENT Tasks
GROUP BY project
;

See attached for results. You can also make some selections to see the difference between a script solution and a chart solution to figure out which one you prefer.