Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Inter record functions (Below)

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/SecCoreSub CorePointsThreshold PointsTarget Points EarnedThreshold VolumeCurrent VolumeTarget VolumeCurrent Volume %
PrimaryVITAMINSVITAMINS ASCORBATES10.856.089,15665,52110,772608.25%
PrimaryVITAMINSVITAMINS ALL OTHERS21.707.848,05737,1599,479392.01%
PrimaryBETA CAROTENEBETA CAROTENE 1%32.5586.27391,323462,875.57%
PrimaryBETA CAROTENEBETA CAROTENE ALL OTHERS32.552.5514,99115,00017,63685.05%
PrimaryVITAMINSVITAMIN A AND E32.553.811,5942,3811,875126.98%
PrimaryWESTLANDWESTLAND43.407.10468,519978,787551,199177.57%
PrimarySOY PROTEINSOY PROTEIN53.000440

CHART 2

Pri/SecCoreSub CorePointsThreshold PointsTarget Points EarnedThreshold VolumeCurrent VolumeTarget VolumeCurrent Volume %Threshold Achieved
PrimaryBETA CAROTENEBETA CAROTENE 1%32.5586.27391,323462,875.57%Yes
PrimaryBETA CAROTENEBETA CAROTENE ALL OTHERS32.552.5514,99115,00017,63685.05%Yes
PrimaryBETA CAROTENECRYSTALLINE FRUCTOSE0-0.00000--
PrimaryBETA CAROTENEDAIRY ALL OTHER0-0.00000--
PrimaryBETA CAROTENEDAIRY GRANULAR0-0.00000--
PrimaryBETA CAROTENEDAIRY SPRAY TATUA0-0.00000--
PrimaryBETA CAROTENEDAIRY TATUA OTHER0-0.00000--
PrimaryBETA CAROTENEGUM ACACIA0-0.00000--
PrimaryBETA CAROTENEOTHER0-0.00000--
PrimaryBETA CAROTENESOY PROTEIN0-0.00000--
PrimaryBETA CAROTENEVITAMIN A AND E0-0.00000--
PrimaryBETA CAROTENEVITAMINS ALL OTHERS0-0.00000--
PrimaryBETA CAROTENEVITAMINS ASCORBATES0-0.00000--
PrimaryBETA CAROTENEWESTLAND0-0.00000--
PrimarySOY PROTEINBETA CAROTENE 1%0-0.00000--
PrimarySOY PROTEINBETA CAROTENE ALL OTHERS0-0.00000--
PrimarySOY PROTEINCRYSTALLINE FRUCTOSE0-0.00000--
PrimarySOY PROTEINDAIRY ALL OTHER0-0.00000--
PrimarySOY PROTEINDAIRY GRANULAR0-0.00000--
PrimarySOY PROTEINDAIRY SPRAY TATUA0-0.00000--
PrimarySOY PROTEINDAIRY TATUA OTHER0-0.00000--
PrimarySOY PROTEINGUM ACACIA0-0.00000--
PrimarySOY PROTEINOTHER0-0.00000--
PrimarySOY PROTEINSOY PROTEIN53.00-0440-N/A
3 Replies
Not applicable
Author

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

Not applicable
Author

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/SecCoreSub CorePointsThreshold PointsTarget Points EarnedThreshold VolumeCurrent VolumeTarget VolumeCurrent Volume %Threshold  Achieved
PrimarySOY PROTEINSOY PROTEIN53.000440N/A
PrimaryWESTLANDWESTLAND43.407.10468,519978,787551,199177.57%Yes
PrimaryBETA CAROTENEBETA CAROTENE 1%32.5586.27391,323462,875.57%Yes
PrimaryBETA CAROTENEBETA CAROTENE ALL OTHERS32.552.5514,99115,00017,63685.05%Yes
PrimaryVITAMINSVITAMIN A AND E32.553.811,5942,3811,875126.98%Yes
PrimaryVITAMINSVITAMINS ALL OTHERS21.707.848,05737,1599,479392.01%Yes
PrimaryVITAMINSVITAMINS ASCORBATES10.856.089,15665,52110,772608.25%Yes
Not applicable
Author

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.

RR