5.x SUMIF Issues
I wanted to see if anyone else was having issues recently with the SUMIF Function on 5.x?
I have an instance on 5.5.4 P2, which has begun to display errors for every SUMIF function in my environment. When I click on the error, I get a system error. In order to see the error, I have had to add a concatenate with "=" in front of my string I am comparing.
I need to calculate the SUM of metric type for the current month within the Company Record, because we have some complex metrics which those stats drive. Because we further calculate the SUM (standard differentiation over many months), I cannot do this through reporting.
The errors is:
_invalid char in front of -
The formula is:
SUMIF(REF([Security Program Metrics],[Helper MM YY Type]),CONCATENATE("=",[Reporting Month_Year],"_Staffing"),REF([Security Program Metrics],[Staffing Delta]))
Helper MM YY Type is a text field helper in our metrics application, which produces a string based on the month, year and selected type value. There are many types that a user can select, and each has its own field set. The helper looks like:
Reporting Month_Year is a calculated text field, which combines the current month and year of the prior month. Since today is January, 2017, this would resolve to:
Staffing Delta is a numeric field with no decimals. Each type has a different numeric field, and each SUMIF references the correct number for the metric type it is summing. It looks like:
When I look at the data, for December, I have 1 record in Security Program metrics with a Helper that is identical to the concatenated value in the formula. That record has a numeric value of 5 in the Staffing Delta field. I would expect the SUMIF to equal 5.
What have I tried?
1) I have ensured that all Program Metrics records have a helper, and the helper is identical to the Reporting Month_Year + the Static type in each calculation.
2) I have checked the calculation order of all fields. All helpers are calculated before the SUMIF is run. All related metrics records have proper order of calculation to populate the fields referenced in the SUMIF.
3) I have tried every combination of adding an "=" to the formula, but cannot get it to work. I added "="&, CONCATENATE("=",...), and I even tried TRIM() (per a suggestion here). I have tried without an =, but then I get a system error and cannot actually read the error message for the formula.
- Am I using the SUMIF incorrectly?
- Did a change recently occur with the way this is calculated? (it used to work fine without "=")
- Is there a known issue when doing a SUMIF with 2 text fields being compared?
- 5.5.4 sp2
- 5.x technical
- Community Thread
- Forum Thread
- RSA Archer
- RSA Archer Suite