Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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

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

1 Reply
mike_garcia
Contributor III

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

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.

Community Browser