# Calculation based on two other sums **URL:** \ **Category:** KReporter\ **Created:** [April 25, 2018, 9:57am UTC](https://community.spicecrm.io/t/calculation-based-on-two-other-sums/292 "2018-04-25T09:57:07Z")\ **Posts on this page:** 18\ **Page:** 1
**Author:** ![FibreCRM\_Simon](https://avatars.discourse-cdn.com/v4/letter/f/f07891/32.png) [@FibreCRM\_Simon](https://community.spicecrm.io/u/FibreCRM_Simon)\ **Post date:** [April 25, 2018, 9:57am UTC](https://community.spicecrm.io/t/calculation-based-on-two-other-sums/292/1 "2018-04-25T09:57:07Z")
Use Case: We have a satisfaction survey based on the famous “NPS” system. In brief this survey asks one question with an answer being a value 0-10. This value is stored in a dropdown field in CRM. The dropdown contain 0 to 10. We want a report to show the percentage of people that rated between 0-6 “detractors”, percentage of people that rated 7 or 8 “passives” and the percentage of people that rated 9 or 10 “promoters”. I think I can produce the above quite easily. However the key to this report is for the company to keep an eye on the overall score which is a formula of [“Promoters” minus “Detractors”] . For example, if 50% of customers rated 9 or 10 (promoters) and 10% rated 6 or less (detractors) the score for the company would be +40. Or if 10% were promoters and 40% detractors the company score would be -30. Ideally I want the company to keep an eye on the overall score and look at ways to improve the score over time using the CRM. I just cant work out how to write a customfuntion or formula etc. Or show the different percentages in the one report across the same field. I hope this makes sense. Can anyone help? Thanks ---
**Author:** ![maretval](https://avatars.discourse-cdn.com/v4/letter/m/b38774/32.png) [@maretval](https://community.spicecrm.io/u/maretval)\ **Post date:** [April 25, 2018, 9:53pm UTC](https://community.spicecrm.io/t/calculation-based-on-two-other-sums/292/2 "2018-04-25T21:53:56Z")
Do you have KReporter Pro? Which version? ---
**Author:** ![FibreCRM\_Simon](https://avatars.discourse-cdn.com/v4/letter/f/f07891/32.png) [@FibreCRM\_Simon](https://community.spicecrm.io/u/FibreCRM_Simon)\ **Post date:** [April 26, 2018, 7:11am UTC](https://community.spicecrm.io/t/calculation-based-on-two-other-sums/292/3 "2018-04-26T07:11:02Z")
HI. we run multiple versions of kReports depending on customer and the requirement for the report will be for all versions. That said, I am building the example on an version with KReports extensions installed and v4.1 Thanks Simon ![nps_score](https://canada1.discourse-cdn.com/flex030/uploads/spicecrm/original/1X/c2dd98da92bcf1caa5932a1d82d51be01e884a01.png) ---
**Author:** ![FibreCRM\_Simon](https://avatars.discourse-cdn.com/v4/letter/f/f07891/32.png) [@FibreCRM\_Simon](https://community.spicecrm.io/u/FibreCRM_Simon)\ **Post date:** [May 7, 2018, 7:44pm UTC](https://community.spicecrm.io/t/calculation-based-on-two-other-sums/292/4 "2018-05-07T19:44:50Z")
Any bright ideas out there that can help? ---
**Author:** ![maretval](https://avatars.discourse-cdn.com/v4/letter/m/b38774/32.png) [@maretval](https://community.spicecrm.io/u/maretval)\ **Post date:** [May 8, 2018, 7:10pm UTC](https://community.spicecrm.io/t/calculation-based-on-two-other-sums/292/5 "2018-05-08T19:10:01Z")
drag the same field multiple times in manipulate tab. or use fixed field holding a formula. How does you manipualte tab look like? ---
**Author:** ![FibreCRM\_Simon](https://avatars.discourse-cdn.com/v4/letter/f/f07891/32.png) [@FibreCRM\_Simon](https://community.spicecrm.io/u/FibreCRM_Simon)\ **Post date:** [May 8, 2018, 7:28pm UTC](https://community.spicecrm.io/t/calculation-based-on-two-other-sums/292/6 "2018-05-08T19:28:13Z")
Hi maretval Thanks for responding. I’ve tried that but cant work out how to deduct a sum from another sum ![2018-05-08_20-26-24](https://canada1.discourse-cdn.com/flex030/uploads/spicecrm/original/1X/d2da21ddb441425fbac0a437891802dbc83178be.png) I know the last field in manipulate tab is wrong but was just trying the ‘cumlated’ value. But this is not the answer I am seeking here. I have to duduct one % from another % ---
**Author:** ![maretval](https://avatars.discourse-cdn.com/v4/letter/m/b38774/32.png) [@maretval](https://community.spicecrm.io/u/maretval)\ **Post date:** [May 8, 2018, 9:06pm UTC](https://community.spicecrm.io/t/calculation-based-on-two-other-sums/292/7 "2018-05-08T21:06:26Z")
ok, I know understand your problem. The sums you have to calculate on are issued by the query itself and you can’t manipulate the results anymore. You will have to trick with a union report, count scores and calculate percentage manually. My example report gives a count of opportunities per size (large, medium, small) where the size corresponds to a range for the amount. large \>= 50000 medium \>=20000 and medium \<50000 small \< 20000 In main screen I dragged the id field 4 times. Renamed first row to total, used function COUNT. The other 3 rows have a fixed value of 0. Additional fields make percentage calculation using formula. ![UnionAdvancedCount01](https://canada1.discourse-cdn.com/flex030/uploads/spicecrm/original/1X/dfabac0bd92ee837ef381915a5df0400da48b10a.PNG) In first Union (meant to count large), I added a where clause amount \>= 50000 in select tab. ![UnionAdvancedCount03](https://canada1.discourse-cdn.com/flex030/uploads/spicecrm/original/1X/4d4b053fb82400ef11c72f368de4b6b2a05abbdb.PNG) In manipulate tab I mapped id field to large. Other fields have fixed value 0. ![UnionAdvancedCount02](https://canada1.discourse-cdn.com/flex030/uploads/spicecrm/original/1X/155ee2b9c582e1082d698d919b1aac8025445080.PNG) In second union (meant for medium) I have again a different where clause: amount \< 50000 and amount \>=20000. In manipulate tab I mapped id field to medium. Other fields have fixed value 0. In third union (meant for small) I have again a different where clause: amount \< 20000. In manipulate tab I mapped id field to small. Other fields have fixed value 0. For Visualization you then need a one dimensional with values. Val ---
**Author:** ![FibreCRM\_Simon](https://avatars.discourse-cdn.com/v4/letter/f/f07891/32.png) [@FibreCRM\_Simon](https://community.spicecrm.io/u/FibreCRM_Simon)\ **Post date:** [May 10, 2018, 11:38am UTC](https://community.spicecrm.io/t/calculation-based-on-two-other-sums/292/8 "2018-05-10T11:38:14Z")
Ok so that works! Thanks. Bit of a long way around but it works. I have taken it one step further by adding a grouping by Assigned To user which is great because Now I can see the feedback scores for each salesperson relative to their own customers that are assigned to them. However now that I have added this grouping to the report I can no longer see the Total overall scores for the company. I have tried to set the report presentation to use the plugin: Standard with Summary, hoping that I will see totals at the base of the report however the report is not displayed. I may have to settle for two reports and just show these on the dashboard. If anyone can suggest a way to achieve the totals then thanks in advance. ![2018-05-10_12-40-42](https://canada1.discourse-cdn.com/flex030/uploads/spicecrm/original/1X/aff5c457ffba9b8e5aececa6ed572e7328f451fc.png) ---
**Author:** ![maretval](https://avatars.discourse-cdn.com/v4/letter/m/b38774/32.png) [@maretval](https://community.spicecrm.io/u/maretval)\ **Post date:** [May 10, 2018, 12:21pm UTC](https://community.spicecrm.io/t/calculation-based-on-two-other-sums/292/9 "2018-05-10T12:21:15Z")
You already picked the right presentation type with “Standard with summary”. Select “sum” in presentation \> column “Function”. You will have to remove “override type” if you set any (I set percentage in my report for % columns). It seems that summaries are done after cells are rendered or so. ![FormulaAddField03](https://canada1.discourse-cdn.com/flex030/uploads/spicecrm/original/1X/cf44e406bb920e58b6d53f33a61816cff86b27d2.png) Val ---
**Author:** ![FibreCRM\_Simon](https://avatars.discourse-cdn.com/v4/letter/f/f07891/32.png) [@FibreCRM\_Simon](https://community.spicecrm.io/u/FibreCRM_Simon)\ **Post date:** [May 31, 2018, 9:47am UTC](https://community.spicecrm.io/t/calculation-based-on-two-other-sums/292/10 "2018-05-31T09:47:46Z")
Thanks. This report is now working although sometimes (a lot of times) the visualization chart(s) do not load and end up just showing “Loading Visualization”. We are on v4.2 It seams to be unpredictable. When I inspect the element in the browser I see the following error. Uncaught TypeError: c is not a constructor at eval (eval at getInstantiator (ext-all.js:22), :3:8) at Object.create (ext-all.js:22) at g. (KReporterViewer.js:12) at Object.each (ext-all.js:22) at g.renderVisualization (KReporterViewer.js:12) at g.success (KReporterViewer.js:12) at Object.callback (ext-all.js:22) at F.onComplete (ext-all.js:22) at F.onStateChange (ext-all.js:22) at XMLHttpRequest. (ext-all.js:22) ![image](https://canada1.discourse-cdn.com/flex030/uploads/spicecrm/original/1X/7b4b81b13a50f9cadac4b1f7e3f88763f4c0cf0f.png) ---
**Author:** ![maretval](https://avatars.discourse-cdn.com/v4/letter/m/b38774/32.png) [@maretval](https://community.spicecrm.io/u/maretval)\ **Post date:** [June 5, 2018, 5:41am UTC](https://community.spicecrm.io/t/calculation-based-on-two-other-sums/292/11 "2018-06-05T05:41:47Z")
What kind of chart did you set using which library? Val ---
**Author:** ![FibreCRM\_Simon](https://avatars.discourse-cdn.com/v4/letter/f/f07891/32.png) [@FibreCRM\_Simon](https://community.spicecrm.io/u/FibreCRM_Simon)\ **Post date:** [June 5, 2018, 7:28am UTC](https://community.spicecrm.io/t/calculation-based-on-two-other-sums/292/12 "2018-06-05T07:28:46Z")
High Charts - Bar Chart however the same issue occurs for other chart types. Sometimes it works but most of the time it fails. Actually here is a video showing the reports sometimes work and sometimes don’t. The only thing I think is happening is that the dashboard is loading too many visualisation dashlets and getting confused. Although saying this once the error appears running a report from KReport fails either to load the plugins or fails to load the visualization ---
**Author:** ![maretval](https://avatars.discourse-cdn.com/v4/letter/m/b38774/32.png) [@maretval](https://community.spicecrm.io/u/maretval)\ **Post date:** [June 5, 2018, 9:45am UTC](https://community.spicecrm.io/t/calculation-based-on-two-other-sums/292/13 "2018-06-05T09:45:20Z")
Thanks for the video! I noticed in last try (opening Report in KReports module) that the path to google charts visulization script is wrong. Do you have any javascript compressor on server that would merge JS files or something like that? Which CRM is it? I can see Suite CRM but based on which Sugar? Anything since Sugar 7 makes problems anyway. ---
**Author:** ![FibreCRM\_Simon](https://avatars.discourse-cdn.com/v4/letter/f/f07891/32.png) [@FibreCRM\_Simon](https://community.spicecrm.io/u/FibreCRM_Simon)\ **Post date:** [June 5, 2018, 9:58am UTC](https://community.spicecrm.io/t/calculation-based-on-two-other-sums/292/14 "2018-06-05T09:58:55Z")
I dont believe we have any compressors like this. The server is a Linux CENTOS standard implementation. The version of SuiteCRM is 7.7 (based on CE 6.5.24) using our own custom theme. ---
**Author:** ![maretval](https://avatars.discourse-cdn.com/v4/letter/m/b38774/32.png) [@maretval](https://community.spicecrm.io/u/maretval)\ **Post date:** [June 6, 2018, 5:50pm UTC](https://community.spicecrm.io/t/calculation-based-on-two-other-sums/292/15 "2018-06-06T17:50:33Z")
mmmh. SuiteCRM 7.7 should be OK. I am sorry I can’t help further. I would need access to the system to debug. And some support fee. ---
**Author:** ![FibreCRM\_Simon](https://avatars.discourse-cdn.com/v4/letter/f/f07891/32.png) [@FibreCRM\_Simon](https://community.spicecrm.io/u/FibreCRM_Simon)\ **Post date:** [June 6, 2018, 6:06pm UTC](https://community.spicecrm.io/t/calculation-based-on-two-other-sums/292/16 "2018-06-06T18:06:00Z")
Ok. I can give you access. Do you need server access or just crm admin access? Which email do I send the details to? You can invoice us as you need. ---
**Author:** ![maretval](https://avatars.discourse-cdn.com/v4/letter/m/b38774/32.png) [@maretval](https://community.spicecrm.io/u/maretval)\ **Post date:** [June 7, 2018, 7:03am UTC](https://community.spicecrm.io/t/calculation-based-on-two-other-sums/292/17 "2018-06-07T07:03:45Z")
Thank you for your trust. Access to crm administration and server will be appreciated. I will first check kreporter installed files. Then try to reproduce the error as you demonstrated in your video and analyze code. Our fee is 100 EUR per hour. I will tell you about progress after 2 hours. Please send credentials to [valerie.maret@twentyreasons.com](mailto:valerie.maret@twentyreasons.com) ---
**Author:** ![maretval](https://avatars.discourse-cdn.com/v4/letter/m/b38774/32.png) [@maretval](https://community.spicecrm.io/u/maretval)\ **Post date:** [June 14, 2018, 1:46pm UTC](https://community.spicecrm.io/t/calculation-based-on-two-other-sums/292/18 "2018-06-14T13:46:53Z")
For KReporter fans following this thread: Sometimes the class used to display the chart is not loaded at the time it is used. A workaround will be implemented in next release (shall be 4.4), checking if chart class is loaded before rendering chart.