asked on April 26, 2022
Hello,
We are attempting to create a formula to reference data in a column titled 'actionName', but cannot figure out the proper format, assuming that one exists.
Loosely written, the formula that we are attempting to achieve is something like this:
(IF('actionName' = 'X') count('actionName') * 25)
OR
(IF('actionName' = 'Y') count('actionName') * 40)
OR
(IF('actionName' = 'Z') count('actionName') * 72)
etc...
Please let us know if there is a possible way to achieve this. We are basically attempting to have a unique multiplier value based on the name of the object under 'actionName'.
Thanks,
Robert
⋅ April 27, 2022
Hello, Robert!
Thank you for contacting us.
First of all, we want to inform you that `if` condition in Flexmonster formulas has the following syntax:
if(condition, then, else)
Since values in pivot are aggregated, you should specify the aggregation function for the field:
if(sum('Price')>0 , ..., ...)
You can find more details about creating formulas in our docs: https://www.flexmonster.com/doc/calculated-values/#formula-operators.
Kindly note that calculated value functionality was intended for values, not hierarchies. As a result, only numeric comparison is supported in the calculated value formula.
Currently, we suggest presenting string data in a numeric format for such cases, e.g.:
'X' => 1
'Y' => 2
'Z' => 3
To display string captions on the grid, you can transform numbers back with customizeCell() function. This approach is illustrated in the following support ticket: https://www.flexmonster.com/question/same-question-as-calculated-value-with-if-statement-comparing-string-values/.
We have also prepared a jsFiddle example for your case: https://jsfiddle.net/flexmonster/rtjbdn8x/.
Hope it helps. Feel free to ask if any further questions arise.
Regards,
Solomiia.
⋅ May 5, 2022
Hello, Robert!
Hope you are having a great week.
Our team is wondering if you had a chance to try the suggested approach of defining calculated values. Could you please let us know if our response helped?
Looking forward to hearing from you.
Regards,
Solomiia.