It took me 15 minutes to realize what I was doing wrong - I had the [x] and the "find this' reversed. But it works for me once the fog had cleared - it is case sensitive, at least going to an Excel source.
I (think) I disagree Shawn - I can have this as a number and then do sums on it without the str part. I just removed the str from your tbwx and got the same result. With that said I've been sat in the hot tub with wine for the last 30 minutes...
Alex, when I did that I got numbers, but as an ATTR() it won't total:
Or if I do a SUM() it give the sum of the products:
Not the 1.99 he was looking for. I know I'm missing out on the wine, but am I missing something else? Joe just checked in for the first time in a while, I'm sure he'll have the answer.
Using the str('1.99') results in Y being formatted as a string, when I need it formatted as a number that I can use for additional calculations, I just need it to be the correct number based upon X, not always 0 or null.
Yeah, I hear you. I couldn't get what you're looking for. But if Alex is out of the hot tub, maybe he's got a solution for you.
Any update on this? Seems like it should be simple but I can't get it to work.
As Shawn mentioned above, getting the grand total will be not be able to be a sum of these numbers (at least not on the same worksheet). The calculation that he provided can be turned into a float by placing the float function in front of the entire calculation:
float(if find([Product], "Amaret")>0 then STR('1.99')
elseif find([Product], "Caf")>0 then STR('2.99')
elseif find([Product], "mile")>0 then STR('3.99')
elseif find([Product], "Tea")>0 then STR('4.99')
Or, don't use the string function and remove the quotation marks. When you place this on the view, these values are going to be multiplied by the number of records, hence the larger values. A second calculation could be created that divides the above calculation by the number of records (or Count of Product). However, when this is placed on the view, the grand total will also be using this aggregation and thus it will not be a sum of the values in the column. One possible option would be to add a running total calculation to this--however, this again would not be what you're looking for in the grand total. (Determining how the grand total is computed is a constant request, that you may want to put under the Ideas section of the Community).
I hope this helps provide some insight!
Putting float around the whole function seems to have done the trick (I really should have tried that). Thanks!