3 Replies Latest reply: Jan 3, 2012 5:55 PM by Roland Kunle RSS

    Inter record functions (Below)

    frank volpe

      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
        • Inter record functions (Below)

          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)
              frank volpe

              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
                • Inter record functions (Below)

                  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