I need to create a pie chart based on below scenario

Question

I have a scenario where I need to  create a pie chart based on a record type for example cmgt_task.I want to show two datas in a pie chart (20%-80%),one having due date as today and completed on as today date and other one will be all open work items that is having due date as today.How can I proceed with this,like do i need to do two seperate queries or it is possible to implement using one query using aggregation.Kindly help me with this scenario ,If I can do it through aggregation then how can I will give different filters for thsese two  datas1.all the completed work items having "due date" as today and "completed on" date as today.And also "is completed "as true and "status" as completeda!queryFilter(
            field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{3d96454c-776f-4b29-9413-d7fffa3ebaeb}statusId',
            operator: "=",
            value: cons!CMGT_REFID_STATUS_TASK_COMPLETED
          ),
          a!queryFilter(
            field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{fb774270-07a3-4a04-93a1-110b11b22d61}isCompleted',
            operator: "=",
            value: true()
          ),
          a!queryFilter(
            field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{62b23693-ed4e-4b5f-8220-777d86be4b8f}completedOn',
            operator: "=",
            value: rule!CMGT_UTIL_ConvertDatetoDatetime(
              date: today(),
              useSystemTimeZone: a!defaultValue(ri!useSystemTimeZoneForDueOn, true)
            )
          )2.all the open work items having "due date "as today and "status" not equal to completed and also" is completed" as falsea!queryFilter(
field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{3d96454c-776f-4b29-9413-d7fffa3ebaeb}statusId',
operator: "",
value: cons!CMGT_REFID_STATUS_TASK_COMPLETED
),
a!queryFilter(
field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{fb774270-07a3-4a04-93a1-110b11b22d61}isCompleted',
operator: "=",
value: false()
),
3.others will be common filter for both as Belowa!queryFilter(
            field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{e3d8da52-b88d-4d50-a94f-d01950cf0c1b}isActive',
            operator: "=",
            value: true
          ),
          a!queryFilter(
            field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{63cb2233-8dd1-4248-9301-e383c85d3a94}isCurrent',
            operator: "=",
            value: true
          ),
          a!queryFilter(
            field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{53258638-2309-421d-a788-6010821afd95}taskIntentionCode',
            operator: "=",
            value: cons!CMGT_WFL_CASE_TYPE_TASK_INTENTION_CODE_WORKFLOW
          ),
          a!queryFilter(
            field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{56f4ac00-4b4b-4c1b-b841-1938bd23ac80}dueOn',
            operator: "=",
            value: rule!CMGT_UTIL_ConvertDatetoDatetime(
              date: today(),
              useSystemTimeZone: a!defaultValue(ri!useSystemTimeZoneForDueOn, true)
            )
          ),
          a!queryFilter(
            field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{63875207-4129-46df-b562-f40a2e867103}taskBehaviorTypeId',
            operator: "",
            value: cons!CMGT_REFID_TASK_BEHAVIOR_TYPE_AUTOMATION
          ),
          a!queryFilter(
            field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{63875207-4129-46df-b562-f40a2e867103}taskBehaviorTypeId',
            operator: "",
            value: cons!CMGT_REFID_TASK_BEHAVIOR_TYPE_AUTOMATION_SEND_JSONRESPONSE
          ),
          a!queryFilter(
            field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.relationships.{1a0492a6-fd9b-466b-b5a1-251704efe784}taskBehaviorType.fields.{4f16406f-3ed9-469a-a757-ddca06cfde53}isAutomated',
            operator: "=",
            value: false()
          ),

mohiniv1048 · Accepted Answer

I tried to implement like this,it is working,but like I just wanted to know is there any better way for this scenario

a!localVariables(
  local!pagingInfo,
  local!showWorkItemsCompletedToday,
  local!showWorkItemsPendingToday,
  local!getWorkItemsDatasubset: index(
    a!queryRecordType(
      recordType: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task',
      filters: a!queryLogicalExpression(
        logicalExpressions: {
          a!queryLogicalExpression(
            operator: "AND",
            filters: {
              a!queryFilter(
                field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{e3d8da52-b88d-4d50-a94f-d01950cf0c1b}isActive',
                operator: "=",
                value: true
              ),
              a!queryFilter(
                field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{63cb2233-8dd1-4248-9301-e383c85d3a94}isCurrent',
                operator: "=",
                value: true
              ),
              a!queryFilter(
                field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{53258638-2309-421d-a788-6010821afd95}taskIntentionCode',
                operator: "=",
                value: cons!CMGT_WFL_CASE_TYPE_TASK_INTENTION_CODE_WORKFLOW
              ),
              a!queryFilter(
                field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{e7b9f82c-d2cd-4057-9815-7cb89ff7f33f}dueOnDate',
                operator: "=",
                value: today()
              ),
              a!queryFilter(
                field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{63875207-4129-46df-b562-f40a2e867103}taskBehaviorTypeId',
                operator: "<>",
                value: cons!CMGT_REFID_TASK_BEHAVIOR_TYPE_AUTOMATION
              ),
              a!queryFilter(
                field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{63875207-4129-46df-b562-f40a2e867103}taskBehaviorTypeId',
                operator: "<>",
                value: cons!CMGT_REFID_TASK_BEHAVIOR_TYPE_AUTOMATION_SEND_JSONRESPONSE
              ),
              a!queryFilter(
                field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.relationships.{1a0492a6-fd9b-466b-b5a1-251704efe784}taskBehaviorType.fields.{4f16406f-3ed9-469a-a757-ddca06cfde53}isAutomated',
                operator: "=",
                value: false()
              ),
              a!queryFilter(
                field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{3d96454c-776f-4b29-9413-d7fffa3ebaeb}statusId',
                operator: "<>",
                value: cons!CMGT_REFID_STATUS_TASK_COMPLETED
              ),
              a!queryFilter(
                field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{fb774270-07a3-4a04-93a1-110b11b22d61}isCompleted',
                operator: "=",
                value: false()
              ),
              
            },
            ignoreFiltersWithEmptyValues: true
          ),
          a!queryLogicalExpression(
            operator: "AND",
            filters: {
              a!queryFilter(
                field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{e3d8da52-b88d-4d50-a94f-d01950cf0c1b}isActive',
                operator: "=",
                value: true
              ),
              a!queryFilter(
                field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{63cb2233-8dd1-4248-9301-e383c85d3a94}isCurrent',
                operator: "=",
                value: true
              ),
              a!queryFilter(
                field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{53258638-2309-421d-a788-6010821afd95}taskIntentionCode',
                operator: "=",
                value: cons!CMGT_WFL_CASE_TYPE_TASK_INTENTION_CODE_WORKFLOW
              ),
              a!queryFilter(
                field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{e7b9f82c-d2cd-4057-9815-7cb89ff7f33f}dueOnDate',
                operator: "=",
                value: today()
              ),
              a!queryFilter(
                field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{63875207-4129-46df-b562-f40a2e867103}taskBehaviorTypeId',
                operator: "<>",
                value: cons!CMGT_REFID_TASK_BEHAVIOR_TYPE_AUTOMATION
              ),
              a!queryFilter(
                field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{63875207-4129-46df-b562-f40a2e867103}taskBehaviorTypeId',
                operator: "<>",
                value: cons!CMGT_REFID_TASK_BEHAVIOR_TYPE_AUTOMATION_SEND_JSONRESPONSE
              ),
              a!queryFilter(
                field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.relationships.{1a0492a6-fd9b-466b-b5a1-251704efe784}taskBehaviorType.fields.{4f16406f-3ed9-469a-a757-ddca06cfde53}isAutomated',
                operator: "=",
                value: false()
              ),
              a!queryFilter(
                field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{3d96454c-776f-4b29-9413-d7fffa3ebaeb}statusId',
                operator: "=",
                value: cons!CMGT_REFID_STATUS_TASK_COMPLETED
              ),
              a!queryFilter(
                field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{fb774270-07a3-4a04-93a1-110b11b22d61}isCompleted',
                operator: "=",
                value: true()
              ),
              a!queryFilter(
                field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{d7e1c9a9-03ae-4bfd-bbe6-2edc7e8d915f}completedOnDate',
                operator: "=",
                value: today()
              )
            },
            ignoreFiltersWithEmptyValues: true
          ),
          
        },
        operator: "OR",
        ignoreFiltersWithEmptyValues: true
      ),
      fields: a!aggregationFields(
        groupings: a!grouping(
          field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{fb774270-07a3-4a04-93a1-110b11b22d61}isCompleted',
          alias: "is_Completed"
        ),
        measures: a!measure(
          field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{4b746ab1-62ac-46bf-9bda-03015c53fc18}taskId',
          function: "COUNT",
          alias: "total_Count"
        )
      ),
      fetchTotalCount: true(),
      paginginfo: a!defaultValue(
        value: local!pagingInfo,
        default: a!pagingInfo(
          startIndex: 1,
          batchSize: 5000,
          sort: a!sortInfo(field: "total_Count", ascending: true)
        )
      )
    ),
    "data",
    {}
  ),
  a!sectionLayout(
    contents: {
      a!columnsLayout(
        columns: {
          a!columnLayout(
            contents: {
              a!cardLayout(
                contents: {
                  a!pieChartField(
                    label: "Total Workitems Completed/Pending Today",
                    labelPosition: "ABOVE",
                    series: {
                      a!forEach(
                        items: local!getWorkItemsDatasubset,
                        expression: a!chartSeries(
                          label: if(
                            fv!item.is_Completed = true(),
                            "WorkItems Completed Today " & "("& fv!item.total_Count &")",
                            " WorkItems Pending Today " & "("& fv!item.total_Count &")"
                          ),
                          data: fv!item.total_Count,
                          links: a!dynamicLink(
                            saveInto: if(
                              fv!item.is_Completed = true(),
                              {
                                a!save(
                                  local!showWorkItemsCompletedToday,
                                  true()
                                ),
                                a!save(local!showWorkItemsPendingToday, null())
                              },
                              {
                                a!save(local!showWorkItemsPendingToday, true()),
                                a!save(
                                  local!showWorkItemsCompletedToday,
                                  null()
                                )
                              }
                            )
                          )
                        )
                      )
                    },
                    showDataLabels: true(),
                    showAsPercentage: true(),
                    allowImageDownload: false(),
                    colorScheme:a!colorSchemeCustom( colors:{"#d6d8db","#5cb85c"}/*{cons!NW_COLOR_PRIMARY,cons!NW_COLOR_SECONDRY}*/),
                    style: "DONUT",
                    seriesLabelStyle: "ON_CHART",
                    height: "MEDIUM"
                  )
                },
                height: "AUTO",
                style: "TRANSPARENT",
                marginBelow: "STANDARD"
              ),
              a!sectionLayout(
                label: "",
                contents: {
                  rule!CMGT_Task_RecordGrid(
                    filters: {
                      a!queryFilter(
                        field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{3d96454c-776f-4b29-9413-d7fffa3ebaeb}statusId',
                        operator: if(
                          a!defaultValue(
                            local!showWorkItemsCompletedToday,
                            false()
                          ) = true(),
                          "=",
                          "<>"
                        ),
                        value: cons!CMGT_REFID_STATUS_TASK_COMPLETED
                      ),
                      a!queryFilter(
                        field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{fb774270-07a3-4a04-93a1-110b11b22d61}isCompleted',
                        operator: "=",
                        value: a!defaultValue(
                          local!showWorkItemsCompletedToday,
                          false()
                        )
                      ),
                      a!queryFilter(
                        field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{63875207-4129-46df-b562-f40a2e867103}taskBehaviorTypeId',
                        operator: "<>",
                        value: cons!CMGT_REFID_TASK_BEHAVIOR_TYPE_AUTOMATION
                      ),
                      a!queryFilter(
                        field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{63875207-4129-46df-b562-f40a2e867103}taskBehaviorTypeId',
                        operator: "<>",
                        value: cons!CMGT_REFID_TASK_BEHAVIOR_TYPE_AUTOMATION_SEND_JSONRESPONSE
                      ),
                      a!queryFilter(
                        field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{843bff08-2ce6-44f3-9d77-422ff2ac0657}isAutomationExecuted',
                        operator: "=",
                        value: false()
                      ),
                      a!queryFilter(
                        field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{e7b9f82c-d2cd-4057-9815-7cb89ff7f33f}dueOnDate',
                        operator: "=",
                        value: today()
                      ),
                      a!queryFilter(
                        field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{d7e1c9a9-03ae-4bfd-bbe6-2edc7e8d915f}completedOnDate',
                        operator: "=",
                        value: today(),
                        applyWhen: a!defaultValue(
                          local!showWorkItemsCompletedToday,
                          false()
                        )
                      )
                    },
                    showAssigneeFilter: false(),
                    pageSize: cons!NW_INT_BATCH_SIZE_SMALL,
                    brandingMap: rule!CMGT_UTIL_LoadBrandingMap(),
                    showSearchBox: true(),
                    showStatusFilter: false(),
                    context: cons!NW_TXT_VIEW_CURRENT_INVENTORY,
                    showDueDateFilter: false(),
                    showWhen: or(
                      local!showWorkItemsCompletedToday,
                      local!showWorkItemsPendingToday
                    ),
                    
                  ),
                  a!buttonLayout(
                    secondaryButtons: {
                      a!buttonWidget(
                        label: "Back",
                        saveInto: {
                          a!save(
                            local!showWorkItemsCompletedToday,
                            null()
                          ),
                          a!save(local!showWorkItemsPendingToday, null)
                        },
                        style: "OUTLINE",
                        color: cons!NW_COLOR_SECONDRY
                      )
                    },
                    showWhen: or(
                      local!showWorkItemsCompletedToday,
                      local!showWorkItemsPendingToday
                    )
                  )
                }
              )
            }
          )
        }
      )
    }
  )
)

shubhama926776 · Answer

You need two separate queries - it's not possible with one aggregated query.
You cannot apply different filters to different groups within a single aggregation. Aggregation groups data based on a field's existing values, but it cannot conditionally categorize records using complex filter logic during the grouping process.

Each group requires its own query with .totalCount, then combine both counts for the pie chart.

Sign up

Login with SSO

Login to the community

Login with SSO