Enforce numerical values for quantitative indicators

[zzgvh]

We should not allow non-numerical values for indicators that aren't qualitative. I'm copying what I wrote in #2756 here:

With the introduction of qualitative indicators I would recommend that we enforce numerical values only for quantitative updates.

To see if this would be hard, I checked how many updates we currently have that would need "fixing", i.e. they are updates with non-numerical values. The results look like this

Value	Count
Empty string	7
Hyphen (-)	5
??	4
Available	13
DWSSP guidelines and DWQS formulated	1
Gender aware	5
Gender neutral	3
High	1
Implementation coordination committee established	1
Maintain inclusion	1
Met	1
N/A	110
No monitoring system in place	1
Not available	10
Now in progress	1
Operational	2
Prototype available	4
Rural water construction standard established	1
See description	4
TBD	2
WASH coordinator position established	1
Yes	2
all	1
available	1
in progress	4
n.a.	1
n/a	3
nd	1
no	1
test	1
yes	5

using this SQL:

SELECT
  u.value,
  count(u.value)
FROM rsr_project p, rsr_result res, rsr_indicator ind, rsr_indicatorperiod per,
  rsr_indicatorperioddata u
WHERE u.value !~ '[0-9]' AND res.project_id = p.id AND ind.result_id = res.id AND
      per.indicator_id = ind.id AND u.period_id = per.id
GROUP BY u.value
ORDER BY u.value

(The SQL can be easily modded to show which projects, results etc the updates belong to)

The Value column is the text entered as data for the update, the Count column is how many updates have this exact value. Note that the first value is an empty string, and the second is only a hyphen, "-", but markdown mess them up 😉. The results above suggest that it should be easy to migrate the values since most of them are an indication that there is no value. The few that hold any data can be moved to the text field for the update.

To see which projects and organisations that have non-numerical values in current indicator updates I ran this SQL:

SELECT DISTINCT
  org.name as Organisation,
  project.id as "Project ID",
  update.value as Value
FROM 
  rsr_project project, 
  rsr_result result, 
  rsr_indicator indicator, 
  rsr_indicatorperiod period,
  rsr_indicatorperioddata update, 
  rsr_partnership partnership, 
  rsr_organisation org
WHERE
  update.value !~ '[0-9]' AND 
  result.project_id = project.id AND 
  indicator.result_id = result.id AND
  period.indicator_id = indicator.id AND 
  update.period_id = period.id AND
  partnership.project_id = project.id AND
  partnership.organisation_id = org.id
ORDER BY org.name, project.id;

Resulting in the following list (Note that there are more records here than above since more than one organisation may be connected to a project and thus to a particular data value.):

Organisation	Project ID	Update value
ADOPEM	3077	N/A
AECID	5893
AFD (France)	5895
AFORD Foundation	3988	N/A
AFORD Foundation	3988	See description
AFORD Foundation	3988	Yes
AFSRT	3983	See description
AMREF Kenya	3981	yes
AMREF Uganda	3983	See description
ANCB Bénin	4135
AST	2977	N/A
Aklilu	4074
Akvo	2074	no
Akvo	2085	all
Akvo	2089	yes
Akvo	2211	Not available
Akvo	2212	Available
Akvo	2212	Not available
Akvo	2213	Available
Akvo	2214	Available
Akvo	2214	Not available
Akvo	2216	yes
Akvo	2780
Akvo	2780	Available
Akvo	2780	Prototype available
Akvo	2783	Available
Akvo	2783	Not available
Akvo	2783	available
Akvo	2846	Operational
Akvo	2846	Prototype available
Akvo	2849	Available
Akvo	2849	Not available
Akvo	2849	Prototype available
Akvo	3979	N/A
Akvo	3979	See description
Akvo	3979	Yes
Akvo	3981	yes
Akvo	3983	See description
Akvo	3988	N/A
Akvo	3988	See description
Akvo	3988	Yes
Akvo	3992	N/A
Akvo	3992	See description
Akvo	5577	Not available
Akvo	5577	Prototype available
Akvo	5702	test
Akvo India	4203
Amref Flying Doctors	3981	yes
Amref Flying Doctors	3983	See description
Amref Flying Doctors	3992	N/A
Amref Flying Doctors	3992	See description
Anonymous	3816	N/A
Aqua for All	3884	No monitoring system in place
Aqua for All	4137
Aqua for All	5257	n.a.
Aqua for All	5257	nd
Aqua for All	5837	TBD
Atcwar	5837	TBD
Austrian Development Agency	5460
BTC	5457
Batu	4074
Bedari	2778	-
Belle Porte	2977	N/A
Both ENDS	3979	N/A
Both ENDS	3979	See description
Both ENDS	3979	Yes
CARE	6405	DWSSP guidelines and DWQS formulated
CARE	6405	Implementation coordination committee established
CARE	6405	Rural water construction standard established
CARE	6405	WASH coordinator position established
CBM	2977	N/A
CLIP	3988	N/A
CLIP	3988	See description
CLIP	3988	Yes
CONANI	2937	N/A
CONANI	2937	n/a
Coalition for Children	2937	N/A
Coalition for Children	2937	n/a
Commune	4135
DFID	6261
DFID	6261	Met
DGIS	4135
DORP	3979	N/A
DORP	3979	See description
DORP	3979	Yes
DRC	5455
EU Delegation Senegal	5891
EU Delegation Senegal	5893
EU Delegation Senegal	5895
EU Delegation Uganda	5457
EU Trust Fund for Africa	5455
EU Trust Fund for Africa	5457
EU Trust Fund for Africa	5460
EU Trust Fund for Africa	5891
EU Trust Fund for Africa	5893
EU Trust Fund for Africa	5895
Ethiopia WASH Alliance	4074
European Commission N.E.	5706
FODDET	2977	N/A
FUNDAPEC	3077	N/A
Fetaph	2977	N/A
Finland MFA	2937	N/A
Finland MFA	2937	n/a
Finland MFA	2938	Gender aware
Finland MFA	2938	Gender neutral
Finland MFA	2938	Maintain inclusion
Finland MFA	2938	N/A
Finland MFA	2940	High
Finland MFA	2940	N/A
Finland MFA	3077	N/A
Finland MFA	3299	N/A
Football for Water	2074	no
GYAM	3988	N/A
GYAM	3988	See description
GYAM	3988	Yes
Geleta Feseha	4074
Getnet Abera	4074
Ghana WASH Alliance	3988	N/A
Ghana WASH Alliance	3988	See description
Ghana WASH Alliance	3988	Yes
HEWASA	3983	See description
HUNDEE	2940	High
HUNDEE	2940	N/A
Handicap International	2977	N/A
Hilton Foundation	2952	Gender aware
Hilton Foundation	2952	N/A
Hoogheemraadschap De Stic	2359	Now in progress
Hoogheemraadschap De Stic	2359	in progress
IED   Afrique	6261
IED   Afrique	6261	Met
IIED	6261
IIED	6261	Met
INADES	3299	N/A
INFOTEP	3077	N/A
INTAGRAD	3988	N/A
INTAGRAD	3988	See description
INTAGRAD	3988	Yes
IOM	5891
IRC	3992	N/A
IRC	3992	See description
IRM	2778	-
Imarisha Naivasha	2359	Now in progress
Imarisha Naivasha	2359	in progress
Isotope Hydrology Lab	5837	TBD
JESE	3983	See description
KEWASNET	3981	yes
KFC	2359	Now in progress
KFC	2359	in progress
KMG-Ethiopia	2940	High
KMG-Ethiopia	2940	N/A
KNVB	2074	no
L&T General Insurance	2097	yes
LID	4135
MOPAFEJ	3299	N/A
Mbarara University	3757	N/A
McGill University	3757	N/A
MetaMeta	5257	n.a.
MetaMeta	5257	nd
Ministry for Foreign Affairs of Finland	2778	-
Ministry for Foreign Affairs of Finland	2942
Ministry for Foreign Affairs of Finland	2942	N/A
Ministry for Foreign Affairs of Finland	2942	n/a
Ministry for Foreign Affairs of Finland	2952	Gender aware
Ministry for Foreign Affairs of Finland	2952	N/A
Ministry for Foreign Affairs of Finland	2977	N/A
Ministry for Foreign Affairs of Finland	3086	Gender aware
Ministry for Foreign Affairs of Finland	3086	N/A
Ministry for Foreign Affairs of Finland	3335	N/A
Ministry for Foreign Affairs of Finland	3757	N/A
Ministry for Foreign Affairs of Finland	3768	Gender neutral
Ministry for Foreign Affairs of Finland	3768	N/A
Ministry for Foreign Affairs of Finland	3816	N/A
Ministry for Foreign Affairs of Finland	3896
Ministry for Foreign Affairs of Finland	3896	N/A
Monde Radieux	2977	N/A
Multiple match funding	2097	yes
NAWAD	3983	See description
NIA	3981	yes
NL Min. Foreign Affairs	2097	yes
NL Min. Foreign Affairs	3992	N/A
NL Min. Foreign Affairs	3992	See description
NLC - consulting	3299	N/A
NZV	2359	Now in progress
NZV	2359	in progress
Near East Foundation	6261
Near East Foundation	6261	Met
Near East Foundation	6282
Netherlands Embassy Kenya	2359	Now in progress
Netherlands Embassy Kenya	2359	in progress
NewEnergy	3988	N/A
NewEnergy	3988	See description
NewEnergy	3988	Yes
OKANI	3299	N/A
OXFAM	6405	DWSSP guidelines and DWQS formulated
OXFAM	6405	Implementation coordination committee established
OXFAM	6405	Rural water construction standard established
OXFAM	6405	WASH coordinator position established
Other contributions	5257	n.a.
Other contributions	5257	nd
PADet	2940	High
PADet	2940	N/A
PRACTICA	4137
PROTOS Mali	4137
Plan Dominican Republic	2937	N/A
Plan Dominican Republic	2937	n/a
Plan Dominican Republic	3077	N/A
Plan Ethiopia	2938	Gender aware
Plan Ethiopia	2938	Gender neutral
Plan Ethiopia	2938	Maintain inclusion
Plan Ethiopia	2938	N/A
Plan Ethiopia	2940	High
Plan Ethiopia	2940	N/A
Plan Finland	2778	-
Plan Finland	2937	N/A
Plan Finland	2937	n/a
Plan Finland	2938	Gender aware
Plan Finland	2938	Gender neutral
Plan Finland	2938	Maintain inclusion
Plan Finland	2938	N/A
Plan Finland	2940	High
Plan Finland	2940	N/A
Plan Finland	2942
Plan Finland	2942	N/A
Plan Finland	2942	n/a
Plan Finland	2952	Gender aware
Plan Finland	2952	N/A
Plan Finland	2977	N/A
Plan Finland	3077	N/A
Plan Finland	3086	Gender aware
Plan Finland	3086	N/A
Plan Finland	3299	N/A
Plan Finland	3335	N/A
Plan Finland	3757	N/A
Plan Finland	3768	Gender neutral
Plan Finland	3768	N/A
Plan Finland	3816	N/A
Plan Finland	3896
Plan Finland	3896	N/A
Plan Finland	5521	-
Plan Kenya	2952	Gender aware
Plan Kenya	2952	N/A
Plan Laos	3335	N/A
Plan Mozambique	3896
Plan Mozambique	3896	N/A
Plan Pakistan	2778	-
Plan Pakistan	3768	Gender neutral
Plan Pakistan	3768	N/A
Plan Sweden	2940	High
Plan Sweden	2940	N/A
Plan Timor-Leste	3086	Gender aware
Plan Timor-Leste	3086	N/A
Plan Togo	2977	N/A
Plan Uganda	3757	N/A
Plan Uganda	3816	N/A
Practical Action Kenya	3981	yes
Presby	3988	N/A
Presby	3988	See description
Presby	3988	Yes
RAIN	3981	yes
RAIN	3983	See description
RAIN	3992	N/A
RAIN	3992	See description
RATSON	2938	Gender aware
RATSON	2938	Gender neutral
RATSON	2938	Maintain inclusion
RATSON	2938	N/A
RUAF	3988	N/A
RUAF	3988	See description
RUAF	3988	Yes
Red Cross NL	4209	??
Red Cross NL	4210	??
Red Cross NL	4215	??
Rotary Enschede	3884	No monitoring system in place
SAATHI	4203
SASOL Foundation	3981	yes
SASOL Foundation	5257	n.a.
SASOL Foundation	5257	nd
SEDUC	3299	N/A
SIDA	2940	High
SIDA	2940	N/A
SIL	3299	N/A
SLOPB	3979	N/A
SLOPB	3979	See description
SLOPB	3979	Yes
SNS Reaal	2097	yes
STVM	3086	Gender aware
STVM	3086	N/A
Simavi	2085	all
Simavi	2089	yes
Simavi	3979	N/A
Simavi	3979	See description
Simavi	3979	Yes
Simavi	3981	yes
Simavi	3983	See description
Simavi	3988	N/A
Simavi	3988	See description
Simavi	3988	Yes
Simavi	3992	N/A
Simavi	3992	See description
Simavi	4323
Simli AiD	3988	N/A
Simli AiD	3988	See description
Simli AiD	3988	Yes
Slamson	5837	TBD
TATA AIG	2097	yes
Titan CSR	4203
UNDP (duplicate)	2977	N/A
UNICEF	2977	N/A
UNICEF Pacific	6405	DWSSP guidelines and DWQS formulated
UNICEF Pacific	6405	Implementation coordination committee established
UNICEF Pacific	6405	Rural water construction standard established
UNICEF Pacific	6405	WASH coordinator position established
UNU Merit	2097	yes
URWA	3983	See description
USAID	6282
USSIA	3983	See description
Universiteit Twente - ITC	2359	Now in progress
Universiteit Twente - ITC	2359	in progress
Urbanet	3988	N/A
Urbanet	3988	See description
Urbanet	3988	Yes
Uttaran	3979	N/A
Uttaran	3979	See description
Uttaran	3979	Yes
VIA Water	4137
VIA Water	5257	n.a.
VIA Water	5257	nd
VIA Water	5837	TBD
VNG International	4135
Vanuatu Department of Water	6405	DWSSP guidelines and DWQS formulated
Vanuatu Department of Water	6405	Implementation coordination committee established
Vanuatu Department of Water	6405	Rural water construction standard established
Vanuatu Department of Water	6405	WASH coordinator position established
WASH Alliance Bangladesh	3979	N/A
WASH Alliance Bangladesh	3979	See description
WASH Alliance Bangladesh	3979	Yes
WASH Alliance Bangladesh	4323
WASH Alliance International	2089	yes
WASH Alliance International	3981	yes
WASH Alliance International	4074
WASH Alliance International	4323
WASH Alliance Kenya	3981	yes
WASH alliance uganda	3983	See description
WASTE	2097	yes
WASTE	3979	N/A
WASTE	3979	See description
WASTE	3979	Yes
WASTE	3981	yes
WASTE	3983	See description
WASTE	3988	N/A
WASTE	3988	See description
WASTE	3988	Yes
WASTE	3992	N/A
WASTE	3992	See description
WASTE	4074
WLN Indonesia	3884	No monitoring system in place
WMD	3884	No monitoring system in place
WODA	2938	Gender aware
WODA	2938	Gender neutral
WODA	2938	Maintain inclusion
WODA	2938	N/A
WRMA	2359	Now in progress
WRMA	2359	in progress
WWF Kenya	2359	Now in progress
WWF Kenya	2359	in progress
Wandelen voor Water	2216	yes
WaterAid Bangladesh	3979	N/A
WaterAid Bangladesh	3979	See description
WaterAid Bangladesh	3979	Yes
WaterAid Bangladesh	4323
Wetlands International	3983	See description
Wetlands International	3992	N/A
Wetlands International	3992	See description
Wetlands International	4209	??
Wetlands International	4210	??
Wetlands International	4215	??
World Vision	6405	DWSSP guidelines and DWQS formulated
World Vision	6405	Implementation coordination committee established
World Vision	6405	Rural water construction standard established
World Vision	6405	WASH coordinator position established
Ylen Hyvä	3086	Gender aware
Ylen Hyvä	3086	N/A
Ylen Hyvä	3335	N/A
Zimmerman & Zimmerman	2846	Operational
Zimmerman & Zimmerman	2846	Prototype available

We need to figure out how to migrate the non-numerical values that currently are in the DB. Most of them seem like "non values" but I think we need to check with the partner team what the values signify to understand how to go about this migration.

[zzgvh]

Investigation around enforcing numeric values

Indicator

Currently we don't store any computed values on the indicator level

IndicatorPeriod

The following fields should be made numeric:

• target_value
• actual_value
• numerator
• denominator

Uses

The fields are used in the following places. Refactoring needed is indicated with bold face

• taget_value
  • IndicatorPeriod.__unicode__() Cast to unicode
  • IndicatorPeriod.percent_accomplishment() Cast to Decimal
  • IndicatorPeriod.save() Copied to child periods if exists
    • IndicatorPeriod.taget() Cast to Decimal, but if this fails return original value.
• actual_value
  • IndicatorPeriod.__unicode__() Cast to unicode
  • IndicatorPeriod.actual() Cast to Decimal, but if that fails return original value if it exists, otherwise IndicatorPeriod.baseline
  • IndicatorPeriod.actual_value_is_decimal Cast to Decimal, if it fails return not self.actual_value
  • IndicatorPeriod.clean()
  • IndicatorPeriod.percent_accomplishment() Cast to Decimal, if it fails return None
  • IndicatorPeriod.save() Assigned to str(percentage)
  • IndicatorPeriod.recalculate_children() Assigned to multiple, possibly string
  • IndicatorPeriod.recalculate_period() Assigned to string
• numerator
  • IndicatorPeriod.save() Used in calculation that is cast to string
  • IndicatorPeriod.recalculate_children() Assigned to result of self.child_periods_percentage() which may return a string
  • IndicatorPeriod.recalculate_period() Assigned to a possible string
• numerator
  • Same as for numerator

IndicatorPeriodData

The following fields should be made numeric:

• value
• period_actual_value

Uses

• value
  • IndicatorPeriodData.clean() Cast to Decimal, checked for existance
  • IndicatorPeriodData.update_new_value() Cast to Decimal and string. Method may return either str or float when type of field is changed
  • IndicatorPeriodData.save() Assigend to result of utils.calculate_percentage()
• period_actual_value
  • IndicatorPeriodData.update_new_value() Cast to Decimal

[stellanl]

How about numerical values using decimal commas?

[punchagan]

Closed in #3076