Tracking measurements changes with set analysis: why not working in a Table with more than one line ?

[#1]

We are trying to understand if a change in a measurement device affects its measurements:

We have a table of data collection objects that collect measurements they are stored in a table like this:

	Table DataCollectObjects
Location	ObjectID	DataOfReplacement
L1	O1	Jan-08
L2	O2	Set-07
L3	O3	Ago-10
…	….	….

The measurements that come from data collection objects are on separate table, that holds the measurements obtained each month for each location

	Table Measurements
Location	Value	DataOfMeasurement
L1	230	Fev-08
L1	340	Mar-08
L2	65	Set-10
L2	375	Out-10
L2	44	Nov-10

The goal is to understand if a change of a data collection object had any influence in the measurements.

Fist we determine the period of analysis before and after the replacement (for 3 months before):

vPeriodBefore = '<$(=Date(Monthstart(DataOfReplacement,0), 'MMM-YYYY'))>$(=Date(Monthstart(DataOfReplacement,-3), 'MMM-YYYY'))'

vAfterPeriod = '>$(=Date(Monthstart(DataOfReplacement,0), 'MMM-YYYY'))<$(=Date(Monthstart(DataOfReplacement,3), 'MMM-YYYY'))'

And then we sum over that periods:

SumBefore = =sum( {$< DataOfMeasurement = {$(= vPeriodBefore)}>} Value )

SumBefore = =sum( {$< DataOfMeasurement = {$(= vPeriodBefore)}>} Value )

The problem is that I cannot obtain a table over "Location" as dimension of SumBefore and Sum After.

If only one Dimension is selected the values display ok. Otherwise it is simple displayed " -"

What am I doing wrong ?

Miguel Maia

[mike_garcia]

Hello,

I'm guessing It only works when one location is selected becasue, when the variables PeriodBefore and PeriodAfter are evaluated, it doesn't know which of all the possible values in DataofReplacement to take, since there are several (I think one for each location).

Try eliminating the variable and moving the calculation into the set expression.

SumBefore: =sum( {$< DataOfMeasurement = {"<$(=Date(Monthstart(DataOfReplacement,0), 'MMM-YYYY'))>$(=Date(Monthstart(DataOfReplacement,-3), 'MMM-YYYY'))"}>} Value )

Mike.