2 Replies Latest reply on Nov 28, 2016 6:13 AM by Gordon Stewart

    Create a trend with no snapshot data

    Gordon Stewart



      I'm trying to build a chart with a rolling 3 months worth of data showing the weekly trend of how many tasks were still open by the end of the week; our week runs from Monday to Sunday


      The problem I have is that I don't have any daily/weekly/monthly snapshot data or I'd just use the task status and count those still open on the Sunday of any given week.


      The (likely flawed) logic I had in my head was to plot a rolling 3 month view using some kind of calendar datasource then for each day work out how many tasks were still Open by the end of the day using the Task Completed dimension; by default the task completed date is set to 31/12/2099 until someone updates it with the actual completion date.


      For the first week being plotted I'd need to firstly calculate how many tasks had been created prior to, and during that week using the created date and then subtract those whose Completed date fell within that week..the balance on the Sunday (our end of week) would be counted as tasks carried forward into the next week > the starting point (carried forward) figure for week 2 and the same logic would apply; add any new tasks created week 2 to the carried forward from the previous week (week 1 in this case) and subtract all those completed in week 2 (some of those completed tasks may have been carried forward from week 1)


      So, the first question is does this sound doable as the view will be a rolling 3 months?  and if it does could someone help with the calculations needed to plot this.


      I've attached the extracted workbook with some sample data I popped into Excel


      Thanks in advance for any help