Calculated Value based on individual cell value criteria
We are starting to get quite a few queries from our power-user customers around the features of flexmonster (eg. Calculated Values). Do you guys have any online documentation focused on users (not developers) around the features available in the UI? Specifically Calculated Values.
The current specific query that I can’t work out – a customer wants to add a calculated value based on criteria from another column.
units:1, rate:1000, total:1000, unitOfMeasure:’payment’
units:40, rate:30, total:1200, unitOfMeasure:’hours’
units:4, rate:60, total: 240, unitOfMeasure:’hours’
They want to add a calculated value called ‘Hours’ which is basically just the value in units when the unitOfMeasure == ‘hours’, otherwise 0. So the first row would contain 0, and the subsequent 2 would contain 40 and 4 respectively. (in the full example they will then pivot this to obtain the sum of hours per employee, without counting units of other types of line items).
However there seems to be no way to introspect the individual values of rows (there is a ‘calculate individual values’ switch, but I can’t see what effect that has).
Thank you for writing.
We are planning to add User Interface section to our website the next week. We will provide you with a link to this section as soon as it is available.
Concerning your specific query, I need to inform you that currently it is not possible to accomplish such functionality. The thing is that
unitOfMeasure is a string value and only
count aggregation is available. Here is why a ‘calculate individual values’ switch does not have any effect. I can offer you the following workaround: please add to your source data new numeric column which will have value
After that, your approach would definitely work for numeric values. I have prepared a sample with a calculated value called
'Formula #3' which is basically just the value in units when the
rate == 30, otherwise
0. Please, have a look: http://jsfiddle.net/flexmonster/pz431qp5/8/. Also, there is a video demonstrating how to add such calculated value via UI: http://take.ms/wec2a.
Please let me know in case of further questions.
Thanks for the prompt reply Tanya. Looking forward to the documentation!
Regarding your proposed workaround, unfortunately that is not possible. The values of unitOfMeasure are not hardcoded, but dynamic based on what the client has configured in the backend. We can’t hardcode a special case for this one client for ‘hours’, as the next client may want it for a different UoM.
Is it possible to add this as a feature request? It would make your calculated values much more useful being able to introspect any existing value of each row.
Thanks for the update.
We have added your request to our customers’ wishlist. Currently there are no plans to add this feature in the next major release. We will notify you about any updates.
I’ve got an update for you, our new User interface section was just posted. You are welcome to check it out.