Comparing current values with historic audit values

[#1]

Hi All,

I have two tables - one contains the current values of a table of projects and the second is an audit table which contains an entry each time a field is change.

Based on these two tables, I need to be able to answer the following types of questions:

• Show all of the projects that had their stage changed to Execute between xx date and xx date
• Show all of the projects that were created this week, this month or between two dates
• Show the previous value of xx field on xx project
• Show when xx field was last changed or display no change

Help please!

Thanks,

A

T1_Current_Project_Table
Project	Project Name	Project Manager	Start Date	Finish Date	Stage	Created
P1	Alpha	Freddy	01/01/2013	26/04/2014	Initiate	01/12/2012
P2	Bravo	Eoin	02/01/2013	27/04/2014	Plan	02/12/2012
P3	Charlie	Daisy	03/01/2013	28/04/2014	Execute	03/12/2012
P4	Delta	Charlie	04/01/2013	29/04/2014	Design	04/12/2012
P5	Echo	Betty	05/01/2013	30/04/2014	Test	05/12/2012
P6	Foxtrot	Adam	06/01/2013	01/05/2014	Initiate	06/12/2012

T2_Project_Table_Audit
Column Changed	Changed By	Changed On	Project	Old Value	New Value
Project Manager	A.N. Other	19/01/2013 03:14	P3	Dora	Daisy
Stage	Joe	01/02/2013 08:17	P4	Initiate	Plan
Stage	Joe	10/04/2013 08:17	P4	Plan	Execute

[Gysbert_Wassenaar]

• Show all of the projects that had their stage changed to Execute between xx date and xx date
  Two helper variables to hold the start and end date for the search: vStartPeriod, vEndPeriod
  Dimension: Project, Project Name
  Expression: sum({<[Old Value]-={'Execute'},[New Value]={'Execute'},[Changed On]={'>=$(vStartPeriod)<=$(vEndPeriod)'}>}1)
  Notes:
  you can hide the expression in a straight table so only the projects are shown
  make sure the [Changed On] dates are numeric dates, not strings.

• Show all of the projects that were created this week, this month or between two dates
  Dimension: Project, Project Name
  expression:
  this week: sum({<[Created]={'>=$(=today()-6)<=$(=today())'}>1)
  this month: sum({<[Created]={'>=$(=monthstart(today())<=$(=today())'}>1)
  between two dates: sum({<[Created]={'>=$(vStartPeriod)<=$(vEndPeriod)'}>1)
  Notes:
  Create and use other variables if you want to keep this independant of the change-to-execute chart

• Show the previous value of xx field on xx project
  Dimension: Project, Project Name, [Column Changed]
  expression: firstsortedvalue([Old Value], -[Changed On])

• Show when xx field was last changed or display no change
  Dimension: Project, Project Name, [Column Changed]
  expression: max([Changed On])
  Notes:
  If there is no record for a field for a product in the audit table it's not possible to show that field. Or you first have to make sure that you created records for all fields for all projects in the audit table

[bill_mtc]

Nice, that's the power of set analysis! Kudos.