JohnArniotis1
3 years agoQrew Member
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
------------------------------
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
------------------------------