Skip to main content
May 17, 2023
Question

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

  • May 17, 2023
  • 1 reply
  • 0 views

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
May 18, 2023

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