14 Replies Latest reply on Mar 28, 2013 1:34 PM by Sarah Gehrke

# Calculating Age and Count using Microsoft Project Data based on Conditions

Hello,

I am trying to create the following data (top) so it can be displayed in a chart like this (bottom). The Colors in the graph would denote Activity. I have attached a sample data file.

### Table 4: Individuals in the Last Process by Activity

 Pending FRA Activity to be Completed Quantityof Individuals in each Activity Count of Individuals who have spent Less than 3 Months in the Activity Count of Individuals who have spent 3 to 6 Months in the Activity Count of Individuals who have spent  6 to 9 Months in the Activity Count of Individuals who have spent  9 to 12 Months in the Activity Count of Individuals who have spent  12 Months or Longer in the Activity Activity 1 Activity 2 Activity 3 Activity 4

The activity durations are each unique and based on conditions:

• ∙ Age of Activity 1 - If ABC = 100% and DEF <100%, then count days from ABC start to today
• ∙ Count of Individuals in Activity 1 - Count if ABC = 100% and DEF <100%
• ∙ Age of Activity 2- If ABC = 100% and GHI < 100%, then count days from ABC start to today
• ∙ Count of Individuals in Activity 2- Count if ABC = 100% and GHI < 100%
• ∙ Age of Activity 3 - If DEF = 100% and JKL <100%, then count days from DEF to today
• ∙ Count of Individuals in Activity 3 - Count if DEF = 100% and JKL <100%
• ∙ Age of Activity 4 - If JKL = 100% and MNO <100%, then count days from JKL to today
• ∙ Count of Individuals in Activity 4 - If JKL = 100% and CFEC <100%
• ###### 1. Re: Calculating Age and Count using Microsoft Project Data based on Conditions

Anybody?

• ###### 2. Re: Calculating Age and Count using Microsoft Project Data based on Conditions

Just wanted to follow up to see if anybody had any advice or suggestions. I would really appreciate anything!

• ###### 3. Re: Calculating Age and Count using Microsoft Project Data based on Conditions

Hi Sarah

Do you have a larger data set?  I've built something, but there are not enough data points to check if it works.  Thanks.

Ashley

• ###### 4. Re: Calculating Age and Count using Microsoft Project Data based on Conditions

Hi Ashley,

Thanks for taking a look! I have doubled the set size. Is that enough. I can build it our further if you need me to.

Thanks!

Sarah

• ###### 5. Re: Calculating Age and Count using Microsoft Project Data based on Conditions

Hi Ashley,

Just wanted to follow up. Did the updated file work? Let me know if there is anything else I can do.

Thanks!

Sarah

• ###### 6. Re: Calculating Age and Count using Microsoft Project Data based on Conditions

Sorry it took so long to get it back to you, works been buzy.  I didn't realize until after I made it that I created it in Tableau 8.  Let me know if you have trouble opening it.

File updated with minor fix.

• ###### 7. Re: Calculating Age and Count using Microsoft Project Data based on Conditions

Not a problem! I appreciate your help. Unfortunately, I couldn't open it because I have Tableau 7. Do you know how/where I can download 8 or is it possible to send me a version that I can download with 7?

• ###### 8. Re: Calculating Age and Count using Microsoft Project Data based on Conditions

Thank Ashley! This was so helpful. I just have one more question. Not every individual always has the same exact steps (i.e. they only have ABC and DEF) and that is messing up the table calculations. Do you have any suggestions for overcoming that? Sorry I didn't build that into my data sample.

• ###### 9. Re: Calculating Age and Count using Microsoft Project Data based on Conditions

I'm not quite sure what's going on as step order shouldn't be affecting activities.

I created a new viz in the attached called Step Completeness, so that you can see each activity definition is independent [Activity].  Activity 1 only looks at ABC and DEF.  Activity 4 is based solely on JKL and MNO regardless of ABC, DEF, GHI data.  Theoretically I should be able to go into the excel file and delete all ABC DEF and GHI data points and still have the same Activity 4 numbers.  The one limitations is using the current calculation you can only have one Activity for each user.

Can you give me a deeper explanation about the problem you're having?

• ###### 10. Re: Calculating Age and Count using Microsoft Project Data based on Conditions

Hi Ashley,

I think what I am trying to describe is a situation where an activity is not in a standard order. For example, in my sample worksheet, GHI is always the the third row down and DEF is always the second row down. Looking at the calculation for 'Activity' the lookup calculation relies on that consistency. The issue that I am trying to overcome is that sometimes GHI could be the fifth step or eighth step. Do you know how I could adjust the calculation to handle that variation in the data?

Thank you again!

• ###### 11. Re: Calculating Age and Count using Microsoft Project Data based on Conditions

Got it.  Let me think on it for a bit and I'll get back to you.

• ###### 12. Re: Calculating Age and Count using Microsoft Project Data based on Conditions

Hi Ashely, ,

Just wanted to follow up with you. Did you get a chance to take a second look?

Thanks!

Sarah

• ###### 13. Re: Calculating Age and Count using Microsoft Project Data based on Conditions

Fell off my radar.   Thanks for the reminder.  Question... Will the activities always go in order?  In the example: Activity 1, Activity 2, Activity 3, etc...

• ###### 14. Re: Calculating Age and Count using Microsoft Project Data based on Conditions

No, not necessarily. Not all Individuals will have every task. So one individual may have activity 1, 2, and 3 and another may have only activity 1 and 3.