Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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
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 |
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