Confiforms - Simple calculations of Sum of different fields

[DannyRue]

Hello together,

I have a problem with calculations. I want to make a Dashbord of different Calculations.

May first problem was, that i want a value of two different databases.
If the Database have one entry its OK, but with more he can't sum it. I use ValueView

It looks like this: sum:([entry._func.queryAndSet(Database:348734834;sum:[entry.Fieldname])]) - The Result is (value1,value2, value3, ... and so on) but he don't sum it.

OK: If this a problem, i try it with one Database, but here also the problem, that i can't calculate in an easy way.

I only want calculate Sum of Field1 - Sum of Field2
If i make Sum of Field1 + Sum of Field2 it works with:
Sum:([entry.Field1][entry.Field2])
Here is everything fine and he calculate it true but if i want to subract Field2 - nothing works.

Have someone an idea to calculate in an easy way with totals of a Database?

Maybe with Plain View and Java Script or something else.
Thats an so easy calculation and have to be possible i think.

Thanks for your help and best regards

[Alex Medved _ConfiForms_ · Accepted solution]

Hi @DannyRue 

Calculations in ConfiForms are done on each record individually and then aggregated if ConfiForms can do that

Alex

[DannyRue]

Hello Alex, thanks for your reply.
Is there no opinion to create a calculation of two sums?

It's also possible to make [count] and calculate with them, than i think there also have been something for numeric calculations.

best regard

[Alex Medved _ConfiForms_]

What exactly do you want to calculate? Could you give me an example?

[DannyRue]

Hi Alex, it's simple calculation

I want to sum two collums of a database.
For Example I have a Database with two numeric fields Field1 and Field2.
There are some entries and now i want to calculate this fields and show them in a ValueView

Example: SUMS of collums
sum:[entry.Field1]              sum all entries: 100 + 100 + 100 = 300

sum:[entry.Field2]              sum all entries: 50 + 50 + 100 = 200

My calculation should be: 300 - 200 = 100 ... and show 100 in the value view

My ideas for this problem:
1. Maybe its possible to make another field where the sums calculated with field definition rules.
2. Java Script - this is maybe the best way, but not so easy i think

If you have any more question, no problem.
Best regards and thanks very Much

[Alex Medved _ConfiForms_]

Hi

Then it is simple in ConfiForms as well..

See this demo: http://recordit.co/KVsuZubDwC

Alex

[DannyRue]

Hi Alex, thanks very much for your help, now it works.
There was one more problem, because it only works if all rows are filled with values, because i had some rows, where only one value are inside but both have to be filled, now i gave the empty ones a zero and it works perfectly.

Thanks and
best regards

[Alex Medved _ConfiForms_]

You can use ZEROIFEMPTY wrapper function to handle cases when empty values should be taken in as a 0

(ZEROIFEMPTY("[entry.Field1]") + ZEROIFEMPTY("[entry.Field2]")) 

Alex

[DannyRue]

Perfect, thanks very much!!!

[DannyRue]

Maybe for all people who want to know how to calculate with sums in two different databases, try this one - it works and is really long but cool :-)

Sum:((ZEROIFEMPTY("[entry._func.queryAndSet(myform:1111222;*;Sum:([entry.fieldname]))]")-ZEROIFEMPTY("[entry._func.queryAndSet(myform:33333222;*;Sum:([entry.fieldname2]))]"))/[entry._count])

This example calulate the Sum of Database1 / Field1 - Sum of Database2 /Field2