topic Re: Using firstsortedvalue with aggr and dates in Visualization and Usability

Using firstsortedvalue with aggr and dates

alan2 — Fri, 19 Jun 2020 13:55:03 GMT

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?

Re: Using firstsortedvalue with aggr and dates

NitinK7 — Fri, 19 Jun 2020 14:55:12 GMT

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.

Re: Using firstsortedvalue with aggr and dates

alan2 — Fri, 19 Jun 2020 15:30:21 GMT

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?

Re: Using firstsortedvalue with aggr and dates

NitinK7 — Mon, 22 Jun 2020 06:43:58 GMT

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.

Re: Using firstsortedvalue with aggr and dates

pc-dkn-afg — Mon, 22 Jun 2020 11:43:04 GMT

Hi Nitin

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

Alan