topic Re: Finding "first" values for line chart with cumulative percentage in App Development

Finding "first" values for line chart with cumulative percentage

wayne-a — Wed, 06 Sep 2017 15:09:58 GMT

Hi, I have a set of data like below. These are tasks that happen during a specific week and take a number of days to complete. I'm trying to build a line chart that would have 3 lines and show by week how many days it took for the tasks to be 50% complete, 80% complete and finally 100% complete. I can get the accumulated percentages for each day value by week in a pivot table but I can't come up with a way to take the first number of days value for a particular percentage and chart it. For example in the data below, I know for the week of 7/30 that after 1 day the tasks were 33.33% complete, after 2 days 66.66% complete, after 4 days 83.33% complete and after 5 days 100% complete. I'd like to chart for that week that it took 2 days to get to (or above) 50% complete, 4 days to 80%, and 5 days to 100%. The three percentage buckets would be separate lines with the y values as the number of days, and the x values as the week starting.

Thanks in advance for any ideas.

Re: Finding "first" values for line chart with cumulative percentage

sunny_talwar — Wed, 06 Sep 2017 15:14:27 GMT

Wayne Antinore wrote:

For example in the data below, I know for the week of 7/30 that after 1 day the tasks were 33.33% complete, after 2 days 66.66% complete, after 4 days 83.33% complete and after 5 days 100% complete.

How do you know this? Is this for a specific Task ID or all Task IDs?

Re: Finding "first" values for line chart with cumulative percentage

wayne-a — Wed, 06 Sep 2017 15:19:40 GMT

Hi Sunny, thanks for the reply. This is for all Task ID in the same week. For the week of 7/30/2017 there were 6 tasks, 2 of them took 1 day to complete so that would be 33.33%

Re: Finding "first" values for line chart with cumulative percentage

sunny_talwar — Wed, 06 Sep 2017 15:31:55 GMT

Something like this?

50% Complete

=Min(Aggr(If(RowNo() >= Round(Count(DISTINCT TOTAL <[Week Starting]> [Task ID]) *0.50), [Days to Complete]), [Week Starting], [Task ID]))

80% Complete

=Min(Aggr(If(RowNo() >= Round(Count(DISTINCT TOTAL <[Week Starting]> [Task ID]) *0.80), [Days to Complete]), [Week Starting], [Task ID]))

100% Complete

=Min(Aggr(If(RowNo() >= Round(Count(DISTINCT TOTAL <[Week Starting]> [Task ID])), [Days to Complete]), [Week Starting], [Task ID]))

Re: Finding "first" values for line chart with cumulative percentage

wayne-a — Wed, 06 Sep 2017 18:44:54 GMT

Hi Sunny,

That's it! Thank you very much. I had to work out the sorting so it worked correctly with the AGGR function. Thanks again!

Wayne