topic Inter record functions (Below) in QlikView

Inter record functions (Below)

Tue, 03 Jan 2012 19:09:02 GMT

I have a chart with the following data (see below chart 1). I'm trying to add a column, "Threshold Achieved" and I need to display 'Yes' in the "Threshold Achieved" column when all Primarys have exceeded the Threshold Points Earned. So I really need to loop through the rows for all primarys to evaluate if a Yes/No should go into the column. Is this possible? I started out trying to use the following expression, but it is generating null records in the chart (see below chart 2). Thanks

If (SalesLevel = 'Primary',

If ([Target Points Earned] < [Threshold Points], 'No',

If ([Target Volume] = 0, 'N/A',

If ([Target Points Earned] >= [Threshold Points], 'Yes',

If (below([Target Points Earned] < [Threshold Points]), 'No')))))

CHART 1

Pri/Sec	Core	Sub Core	Points	Threshold Points	Target Points Earned	Threshold Volume	Current Volume	Target Volume	Current Volume %
Primary	VITAMINS	VITAMINS ASCORBATES	1	0.85	6.08	9,156	65,521	10,772	608.25%
Primary	VITAMINS	VITAMINS ALL OTHERS	2	1.70	7.84	8,057	37,159	9,479	392.01%
Primary	BETA CAROTENE	BETA CAROTENE 1%	3	2.55	86.27	39	1,323	46	2,875.57%
Primary	BETA CAROTENE	BETA CAROTENE ALL OTHERS	3	2.55	2.55	14,991	15,000	17,636	85.05%
Primary	VITAMINS	VITAMIN A AND E	3	2.55	3.81	1,594	2,381	1,875	126.98%
Primary	WESTLAND	WESTLAND	4	3.40	7.10	468,519	978,787	551,199	177.57%
Primary	SOY PROTEIN	SOY PROTEIN	5	3.00		0	44	0

CHART 2

Pri/Sec	Core	Sub Core	Points	Threshold Points	Target Points Earned	Threshold Volume	Current Volume	Target Volume	Current Volume %	Threshold Achieved
Primary	BETA CAROTENE	BETA CAROTENE 1%	3	2.55	86.27	39	1,323	46	2,875.57%	Yes
Primary	BETA CAROTENE	BETA CAROTENE ALL OTHERS	3	2.55	2.55	14,991	15,000	17,636	85.05%	Yes
Primary	BETA CAROTENE	CRYSTALLINE FRUCTOSE	0	-	0.00	0	0	0	-	-
Primary	BETA CAROTENE	DAIRY ALL OTHER	0	-	0.00	0	0	0	-	-
Primary	BETA CAROTENE	DAIRY GRANULAR	0	-	0.00	0	0	0	-	-
Primary	BETA CAROTENE	DAIRY SPRAY TATUA	0	-	0.00	0	0	0	-	-
Primary	BETA CAROTENE	DAIRY TATUA OTHER	0	-	0.00	0	0	0	-	-
Primary	BETA CAROTENE	GUM ACACIA	0	-	0.00	0	0	0	-	-
Primary	BETA CAROTENE	OTHER	0	-	0.00	0	0	0	-	-
Primary	BETA CAROTENE	SOY PROTEIN	0	-	0.00	0	0	0	-	-
Primary	BETA CAROTENE	VITAMIN A AND E	0	-	0.00	0	0	0	-	-
Primary	BETA CAROTENE	VITAMINS ALL OTHERS	0	-	0.00	0	0	0	-	-
Primary	BETA CAROTENE	VITAMINS ASCORBATES	0	-	0.00	0	0	0	-	-
Primary	BETA CAROTENE	WESTLAND	0	-	0.00	0	0	0	-	-
Primary	SOY PROTEIN	BETA CAROTENE 1%	0	-	0.00	0	0	0	-	-
Primary	SOY PROTEIN	BETA CAROTENE ALL OTHERS	0	-	0.00	0	0	0	-	-
Primary	SOY PROTEIN	CRYSTALLINE FRUCTOSE	0	-	0.00	0	0	0	-	-
Primary	SOY PROTEIN	DAIRY ALL OTHER	0	-	0.00	0	0	0	-	-
Primary	SOY PROTEIN	DAIRY GRANULAR	0	-	0.00	0	0	0	-	-
Primary	SOY PROTEIN	DAIRY SPRAY TATUA	0	-	0.00	0	0	0	-	-
Primary	SOY PROTEIN	DAIRY TATUA OTHER	0	-	0.00	0	0	0	-	-
Primary	SOY PROTEIN	GUM ACACIA	0	-	0.00	0	0	0	-	-
Primary	SOY PROTEIN	OTHER	0	-	0.00	0	0	0	-	-
Primary	SOY PROTEIN	SOY PROTEIN	5	3.00	-	0	44	0	-	N/A

Inter record functions (Below)

Tue, 03 Jan 2012 19:38:34 GMT

Hello,

for me it looks like your expression isn't valid (or has a null as result) always when [Threshold Points] isn't valid (or null). You can see it starting with line three. So try to find out how to "repair" this field.

Note:

In the last line you got a valid expression although [Target Points Earned] isn't valid. This is luck, better to say because of the above() function.

HtH

Roland

Inter record functions (Below)

Tue, 03 Jan 2012 20:42:19 GMT

When I remove the last if statement I get these results with no null records. I thought the "below" expression would just evaluate the row below the current one.

Pri/Sec	Core	Sub Core	Points	Threshold Points	Target Points Earned	Threshold Volume	Current Volume	Target Volume	Current Volume %	Threshold Achieved
Primary	SOY PROTEIN	SOY PROTEIN	5	3.00		0	44	0		N/A
Primary	WESTLAND	WESTLAND	4	3.40	7.10	468,519	978,787	551,199	177.57%	Yes
Primary	BETA CAROTENE	BETA CAROTENE 1%	3	2.55	86.27	39	1,323	46	2,875.57%	Yes
Primary	BETA CAROTENE	BETA CAROTENE ALL OTHERS	3	2.55	2.55	14,991	15,000	17,636	85.05%	Yes
Primary	VITAMINS	VITAMIN A AND E	3	2.55	3.81	1,594	2,381	1,875	126.98%	Yes
Primary	VITAMINS	VITAMINS ALL OTHERS	2	1.70	7.84	8,057	37,159	9,479	392.01%	Yes
Primary	VITAMINS	VITAMINS ASCORBATES	1	0.85	6.08	9,156	65,521	10,772	608.25%	Yes

Inter record functions (Below)

Tue, 03 Jan 2012 22:55:02 GMT

Hi,

as I said before, I do not think the above()-function does not work. Your column Treshold Points seems for some rows not valid. Try to proof this (and the above()-function) while using something like:

. . .

If (below([Target Points Earned] < 0), 'No')))))

or another value of your choice.