We have a dataset called “MDA LF” and it has a data element called “Endemicity” which can accept integer values from 1 to 5. This data element uses Min() aggregation.
I was hoping to use a sub expression in a new indicator to count the number of IUs (OUs) that have a particular endemicity value.
Imagine we have the following OU hierarchy
Country
State
IU <- dataset is assigned to this level
The country might contain 50 IUs, and in the year 2020 12 of them reported an endemicity value of 1. Therefore we would like this indicator to read read 12 when querying that year. This is not what I am seeing though when I build a pivot table in the data visualiser.
This is what I wrote in the indicator’s numerator definition:
subExpression( if(#{Endemicity} === 1, 1, 0) )
The pivot table is being filtered at either Country or State level, the year 2020 is selected. Endemicity and this indicator are chosen as data fields.
Here is the actual IU level data from Bengo showing the IU endemicities. Judging from this set of results I would expect Bengo state to have 5 endemic IUs, not 0.
Is this how sub expressions are meant to work or am I barking up the wrong tree? How could I achieve this functionality if I am going about this the wrong way?
Subexpressions is new to me so I’m going through creating an indicator but I noticed this:
using the three equal marks shows an error “Expression is not well-formed” did you actually use that === or was this a typo here in the post? (two equal signs shows the expression as valid)
Either way, I’m reviewing and if I find a solution will let you know. I will see if I can get further support on this.
@HaydnJ I have more questions to ask please, is the dataset monthly or yearly? If it is monthly does displaying the pivot table in monthly format display the correct result?
@HaydnJ I also see that a change in columns/rows might help display the numbers correctly, please see if changing those between columns, rows, and filter works. I suggest to check if moving OUs to columns or to rows works rather than in filter.
And I’d like to request from you please if you can help me understand the difference between using the expression subexpression or not using it at all in the if conditional expression: if(#{Endemicity} == 1, 1, 0) vs subExpression( if(#{Endemicity} == 1, 1, 0) )
As I understand, the indicator is working as expected. By default, the indicator takes the aggregation type of the DE used in the indicator. As the DE is using Min aggregation type, the indicator also uses Min to aggregate the indicator. As Pango Aluquem is having a value of 3, as per the indicator formula, it would be evaluated to 0. And the aggregation type of min would pick min(1,1,1,1,1,0) ==> 0 for Bengo.
I never tried this but to get the value that is intended (5), I think you might need to use aggregationType indicator function and change the aggregation type of the indicator to SUM.
I will also try to experiment with this and let you know if I find anything.
Hi @Gassim, the === was a typo, I get confused with javascript quite a bit.
Either way I think Rithvik gor me on the right path. The indicator was inherriting it’s aggregation type from the data element I was using, so I overrided that with a sum expression.
It appears to be working as expected now. Many thanks.
I didn’t see this post until now, but thanks @rithvik for the spot-on analysis. I’m glad to see that subExpression and aggregationType are being useful. Don’t hesitate to tag me on any further issues or questions about these if I can help!
P.S. I apologize that subExpression hasn’t yet made it into the documentation. I need to fix that. Maybe I can add a note about how it works with aggregationType as you’ve demonstrated!
I included a note about how to use the .aggregationType() function when the data element doesn’t have a default aggregation type of SUM. Thanks again for bringing this to my attention (and figuring it out!)
