topic Re: Comparing current values with historic audit values in QlikView

Comparing current values with historic audit values

Wed, 23 Oct 2013 23:11:09 GMT

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,

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

Re: Comparing current values with historic audit values

Gysbert_Wassenaar — Thu, 24 Oct 2013 07:35:06 GMT

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

Re: Comparing current values with historic audit values

bill_mtc — Thu, 24 Oct 2013 07:52:03 GMT

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