Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I need to be able to show enrollment in a programme on the first date in the filtered period as well as the enrollement in the programme on the last date in the filtered period (there are multiple entries for each location). The data has to be displayed by location, gender and age.
I am using the formualas:
FirstSortedValue(mam_total_enrolled,aggr(min(DATE(date)), Gender, Age_category, village))
FirstSortedValue(mam_total_enrolled, - aggr(Max(Date(date)), Gender, Age_category, village))
The first formula seems to work and returns the enrollment from the earliest entry in the reporting period. The second formula however returns the same values as the first formula (the starting enrollment). My understanding is that inserting the - before aggr should return the data from last date in the data set. What formula should I be using to display the final enrollment (from the last day) of the reporting period?
Hi,
try below expression then will be appear totals in table
Sum( Aggr(FirstSortedValue(mam_total_enrolled, date),village,Age_category,Gender))
Sum( Aggr(FirstSortedValue(mam_total_enrolled, -date),village,Age_category,Gender))
Thanks,
Nitin.
Hi
try below expression it is auto aggr in table because of the field
FirstSortedValue(mam_total_enrolled, date).
FirstSortedValue(mam_total_enrolled, -date)
it is work for me.
Thanks NitinK7
Your solution works for me and is much simplier than what I was trying. I guess I was over thinking things.
One follow up question: In the table totals don't appear for Programme enrollment (last 2 lines) which should be the sum of all enrollment of the selected sites. Any ideas?
Hi,
try below expression then will be appear totals in table
Sum( Aggr(FirstSortedValue(mam_total_enrolled, date),village,Age_category,Gender))
Sum( Aggr(FirstSortedValue(mam_total_enrolled, -date),village,Age_category,Gender))
Thanks,
Nitin.
Hi Nitin
Thanks for the solution. Those formulas do exactly what were expecting.
Alan