Forum Discussion

JohnArniotis1's avatar
JohnArniotis1
Qrew Member
3 years ago

Formula Query with filters

Good Afternoon,

I am looking to come up with a formula query, in a formula numbers field, that can calculate a score but also react to filters when placed on a KPI (gauge) report or dashboard. I created a simple query that gives me the correct value but if I add field references and in this case they refer to each other, waiting for input from the user the calculations are off.

Is it possible to add references to the query below that would allow a user to edit it on a report? I am basically looking to recreate a version of SUMPRODUCT from Excel.
(I know that I could have done this differently but with the order of operations in QB this is what worked from an expected value standpoint)

var number INVYES = Size(GetRecords("{31.EX.'Yes'}"));
var number INVNO = Size(GetRecords("{31.EX.'No'}"));
var number POLYYES = Size(GetRecords("{32.EX.'Yes'}"));
var number POLYNO = Size(GetRecords("{32.EX.'No'}"));
var number RORYES = Size(GetRecords("{82.EX.'Yes'}"));
var number RORNO = Size(GetRecords("{82.EX.'No'}"));
var number TotalYES = $INVYES+$POLYYES+$RORYES;
var number TotalNO = $INVNO+$POLYNO+$RORNO;
var number TotalMinus = $TotalYes-$TotalNo;
var number TotalPlus = $TotalYes+$TotalNo;

$totalminus/$totalplus

------------------------------
John Arniotis
------------------------------
No RepliesBe the first to reply