# Calculation based on two other sums

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

Do you have KReporter Pro? Which version?

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

Any bright ideas out there that can help?

drag the same field multiple times in manipulate tab.
or use fixed field holding a formula.
How does you manipualte tab look like?

Hi maretval

Thanks for responding. I’ve tried that but cant work out how to deduct a sum from another sum

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 %

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.

In first Union (meant to count large), I added a where clause amount >= 50000 in select tab.

In manipulate tab I mapped id field to large. Other fields have fixed value 0.

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

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.

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.

Val

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)

What kind of chart did you set using which library?
Val

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

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.

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.

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.

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.