There was a previous question on this but the question wasn't answered since the person realized the problem was something else.
I'm using script to generate a expression that is over 1000 line long, and it doesn't seem to be going through. This is the error I'm getting. I reduced the line counts of the expression and the error seems to disappear at around 200 lines. So, is there a character limit to how long expression can be?
Alternatively, is there a better way to implement 2D arrays with AppSheet that isn't 1000 line of nested IFS()? Specifically what I'm trying to do is implement this weight by age chart (attached) for children growth to get the percentile a child is in.
Solved! Go to Solution.
Yes, as @Suvrutt_Gurjar suggests you want to use a lookup table. You would have it setup something like this:
ID | Age(Months) | Min Weight | Max Weight | Percentile |
71352216 | 2 | 0 | 4.4 | 0th-3st |
32836258 | 2 | 4.4 | 4.5 | 3rd-5th |
68707655 | 2 | 4.5 | 4.9 | 5th-15th |
44294852 | 2 | 4.9 | 5.1 | 15th-25th |
97700600 | 2 | 5.1 | 5.6 | 25th-50th |
31918377 | 2 | 5.6 | 6 | 50th-75th |
29888201 | 2 | 6 | 6.3 | 75th-85th |
... | ... | ... | ... | ... |
To use this table, you would ask in a Form for the Age and Weight of the child. Then you would use those in an expression like below to lookup the Percentile:
ANY(SELECT(Percentiles[Percentile], AND([Age] = [_THISROW].[Age],
[Min Weight] > [_THISROW].[Weight],
[_THISROW].[Weight] <= [Max Weight])))
There is work in creating the table but its a one time effort, unless the Percentiles shift but then it's just simple adjustments in the table.
User | Count |
---|---|
35 | |
9 | |
3 | |
3 | |
2 |