Skip to main content
Known Participant
May 17, 2023 6:40 PM UTC
Question

Trying to get rows mapped together entries that fall under the same month

Trying to map entries that full under the same month together and return count for total entries in that month 

with(
  local!metrics: rule!OCSC_getNumOfTierTwoRequestsSubmitted(
    startDate: ri!startDate,
    endDate: ri!endDate,
  ),
  local!asmts: a!forEach(
    items: local!metrics,
    expression: {
      svc_request_id: fv!item.svc_request_id,
      totalServicesPerformed:  fv!itemCount,
      date: fv!item.created_datetime,
      month: month(fv!item.created_datetime),
      year: year(fv!item.created_datetime)
    }
  ),
  local!mergedRows: local!asmts,
  /* create list of maps with sum of collective asmts done by year-month */
  local!dates: a!forEach(
    items: local!mergedRows,
    expression: { month: fv!item.month, year: fv!item.year }
  ),
  local!datesDist: union(local!dates, local!dates),
  local!prepared: a!forEach(
    items: local!datesDist,
    expression: with(
      local!dateIndex: fv!item,
      a!map(
        month: fv!item.month,
        year: fv!item.year,
        date: concat(fv!item.month, "/", fv!item.year),
        totalServicesPerformed: sum(
          index(
            a!forEach(
              items: local!mergedRows,
              expression: if(
                and(
                  fv!item.month = local!dateIndex.month,
                  fv!item.year = local!dateIndex.year
                ),
                fv!item,
                {}
              )
            ),
            "totalServicesPerformed"
          )
        )
      )
    )
  ),
  todatasubset(
    local!prepared,
    a!pagingInfo(
      startIndex: 1,
      batchSize: - 1,
      sort: {
        a!sortInfo(field: "year", ascending: true),
        a!sortInfo(field: "month", ascending: true)
      }
    )
  ).data
)

Right now I am getting the incorrect count. Count should be 1 count for the month of April and 3 counts for the month of May. 

1 reply

stefanhelzle0001
Brainy
May 18, 2023 6:37 AM UTC

Is there a reason to not use the build it aggregation capabilities?