Problem with set analysis, less / greater than - reference to separate table

[falko_thom]

Hello,

I got some problem I quite don't understand.

There are two different tables. One:

value	min_days	max_days
0.25	20	999
0.81	10	20
1.38	5	10
1.94	3	5
2.50	0	3

Second:

date	yr_qu	action	duration
2012-11-23	2012-3	a	1
2012-11-24	2012-3	a	8
2012-11-25	2012-3	a	3
2012-11-24	2012-3	b	7
2012-11-26	2012-3	b	2
2012-11-26	2012-3	a	1
2012-11-27	2012-3	a	1
2012-11-25	2012-3	b	5
2012-11-28	2012-3	a	10
2012-11-27	2012-3	b	19

I want to work out a table with the dimensions action, yr_qu.

Fomulas:

• avg(duration) named as q_Dur
• value depending on the duration: Avg ( {<q_Dur  = { '>[min_days]<=[max_days]'}>} value)

My new table shows action, yr_qu and q_Dur with correct values.

But the new value always shows the aggregate of alle yr_qu depending on the chosen action.

What am I doing wrong?

I am really thankful for your help!

[Gysbert_Wassenaar · Accepted solution]

1. If q_Dur is an expression or a calculated dimension instead of a field created in the script then you cannot use it as field name in the set modifier.

2. intra-record comparisons like SomeField = { '>[min_days]<=[max_days]'} often don't work unless SomeField uniquely identifies the records for which you want to aggregate. See attached example for an explanation.

That means you probably have to use an if statement instead.