Adding data series together to give a calculated value

David Stevenson -

Sometimes it is much nicer to read a report of two values as the sum of those two values, rather than see them as two separate rows of values per report entity (client / job / phase etc).  There are two ways this can be achieved in the data viewer.  Addition of series is possible, as is subtraction of one or more series from another.

For example you might want to see actual time posted to a job plus future bookings as a single amount.  Or you might want to see estimated time less actual time on a job.  The simplest way to achieve this is to accumulate all the values per job into a single value.  To do this tick the box indicated below in the area where you add your column headings:


This removes the column called Data type and adds up all the numeric rows to give a single value for whatever you are reporting on.  Obviously take care not to add apples and oranges - gross profit plus hours posted is not going to be meaningful.  In order to subtract one value from another you need to first select which data series to be the negative by ticking the box indicated below when adding the new data series.  Or you can turn it from positive to negative values after it has been added by editing the series:



This is fine as long as you are happy to have all the values displayed consolidated to a single value.  However if you have three values, lets say estimated hours, actual hours and booked hours, you might want to see estimated hours vs actual plus booked.  This can also be achieved as follows.  Add all three data series:


Now edit the series name of time actual and time booked to be the same, Actual plus Booked, for example:


This time you want to uncheck the box to hide the data type column, otherwise you'll just get all three added together.  When you run this data viewer report you will now get two rows of data per entity (client / job / phase etc), one for "Actual plus Booked" and one for "Time Estimate".


As a final tip, bear in mind that your data series do NOT all have to share the same date range.  Adding data series together is a great example of why you might not want to have them all use the same date range.  In the first example of actual plus booked hours you might specify actual hours from the start of the year up to the end of last month and booked hours from the start of this month onwards to the end of the year.  That way you avoid adding together actual and booked for the same day.  Similarly total invoiced plus quoted can be used for sales invoices up to a point in time and then quoted price from that point onwards.

Have more questions? Submit a request


Please sign in to leave a comment.
Powered by Zendesk