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

falko_thom — Tue, 18 Jun 2013 11:07:44 GMT

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!

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

Gysbert_Wassenaar — Tue, 18 Jun 2013 17:15:10 GMT

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.

topic Re: Problem with set analysis, less / greater than - reference to separate table in QlikView

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

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