Using firstsortedvalue with aggr and dates

[alan2]

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?

[NitinK7]

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.

[alan2]

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?

[NitinK7 · Accepted solution]

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.

[pc-dkn-afg]

Hi Nitin

Thanks for the solution. Those formulas do exactly what were expecting.

Alan