🍉 Meet Flexmonster Pivot Table & Charts 2.9.Check out all the hot features!
Get Free Trial

Calculated Value based on individual cell value criteria

Tyson asked on October 25, 2017

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.
Source data:
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).
Screenshot attached.


4 answers

Tanya Gryshko Tanya Gryshko Flexmonster October 25, 2017

Hello, Tyson,
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 1 when unitOfMeasure is 'hours' and 0 otherwise.
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.

Tyson October 25, 2017

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.

Tanya Gryshko Tanya Gryshko Flexmonster October 25, 2017

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.

Tanya Gryshko Tanya Gryshko Flexmonster November 1, 2017

Hello, Tyson,
I’ve got an update for you, our new User interface section was just posted. You are welcome to check it out.

Please login or Register to Submit Answer