Milestone Variance between 2 Selected Snapshots

[#1]

I'm trying to create an expression that will calculate the difference in days between milestone dates from two selected "snapshots" of data. My data looks like this:

Snapshot 1, Project A, Milestone 1, Date

Snapshot 1, Project A, Milestone 2, Date

Snapshot 1, Project A, Milestone 3, Date

Snapshot 1, Project B, Milestone 1, Date

Snapshot 1, Project B, Milestone 2, Date

Snapshot 1, Project B, Milestone 3, Date

Snapshot 1, Project C, Milestone 1, Date

Snapshot 1, Project C, Milestone 2, Date

Snapshot 2, Project A, Milestone 1, Date

Snapshot 2, Project A, Milestone 2, Date

Snapshot 2, Project A, Milestone 3, Date

Snapshot 2, Project B, Milestone 1, Date

Snapshot 2, Project B, Milestone 2, Date

Snapshot 2, Project B, Milestone 3, Date

Snapshot 2, Project C, Milestone 1, Date

Snapshot 2, Project C, Milestone 2, Date

etc.

Where I have about 20 snapshots, 100s of projects, and dozens of different milestones.

I want the user to select 2 snapshots and any number of milestones, and be presented with a chart (or table) showing the difference in days between the milestones, i.e., Milestone Date (2nd snapshot) minus Milestone Date (1st snapshot), for analogous milestones. Presumably, I would aggregate using an average to summarize the Project dimension to its Program (a collection of Projects).

I'm somewhat new to QV, so I need help in setting up this expression. Will it involve set analysis, the GetFieldSelections function, among other things? What's the easiest way to do this?

Thanks in advance,

Scott Arnold

[#2]

If you are using QlikView 11, then you want use Alternative States. Take a look at the product grouping tab in What's New in QlikView 11 demo and see if it will do what you want. For more information on how to use alternative states, you can take a look at the qlikview reference manual or the in app help.

[#3]

I'm making some progress, but I must confess that QlikView has got to be the most frustrating tool I've ever used. Documentation is scant at best and things do not appear to work as advertised. Okay, enough venting.

I don't think alternate states should be needed for this. I've created a straight table chart that gets me part of the way using the following expressions:

S1 MS Date: Avg({$<[Baseline.Snapshot Name]={'2011 OCT LBE Final'}>} [Milestone.LBE MS Date])
S2 MS Date: Avg({$<[Baseline.Snapshot Name]={'2012 MAY LBE Final'}>} [Milestone.LBE MS Date])
Variance:       If(IsNull([S1 MS Date]),0,If(IsNull([S2 MS Date]),0,[S1 MS Date] - [S2 MS Date]))

I've defined the following variables:

vSnapshots   =GetFieldSelections([Baseline.Snapshot Name],'|',2)
vSnapshot1   =Left(vSnapshots,Index(vSnapshots,'|',1)-1)
vSnapshot2   =Right(vSnapshots,Len(vSnapshots)-Index(vSnapshots,'|',1))

I can display the values for these variables in text boxes and vSnapshot1 shows "2011 OCT LBE Final" and vSnapshot2 shows "2012 MAY LBE Final" (minus the quotes, of course). I then go back to my table and add an expression that is the same as S1 MS Date above, using variable vSnapshot1, as follows:

Avg({$<[Baseline.Snapshot Name]={$(vSnapshot1)}>} [Milestone.LBE MS Date])

The $(vSnapshot1) shows in the bold gray italics indicating I've done things right, I think, but I don't get the same values in my new column that show up in the S1 MS Date column - all of them are nulls.

What am I doing wrong?!