I am in the process of updating an existing Analytics Report we have for reporting detailed portfolio status for all of our projects and would like clarification on two areas for filtering the correct information:
1) In one widget, we have a pivot table with one column showing project name and the next showing project status. We need to add a new column next to the current project status column that shows the status of the project at a particular point in the past (i.e. status 7 days ago or status 30 days ago). I initially tried to do this by adding "Project Status Change History Old Status" then filtering it by Ranking top 1 "Project Status Change History Change Date" however this has not worked - it appears this is just returning the first status in the workflow for every project, i.e. the same result in every row. If i change this to Bottom 1 ranking it shows my the last status in the workflow for all rows so looks like I am not on the right lines. Screenshot below:
2) I would like to know how to create a widget that returns list of projects where a task has NOT been created in a given timeframe, i.e. last 7 days. We use a custom task level item type for our project managers to submit weekly status reports ( new item created each week for that week's report). We have a widget displaying all the report submissions for that week but need a way to easily view which projects have not had this custom item type added in the last week. I attempted to create a pivot table, filtering Task creation date by timeframe with an offset but this only returns previous weeks report, hiding that week's report. What I need essentially is a way to see the absence of a new task created in a given time or a negative filter of some sorts. Is this possible or could you please suggest an appropriate workaround?
Thanks in advance,