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 datas

1.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 completed

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.{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 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()
),

3.others will be common filter for both as Below

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.{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()
          ),

Shubham Aware · 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.

Harsha Sharma · Answer

Hi Mohini

this part of your code

   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
              ),

can be replaced with

  a!queryFilter(
                field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{63875207-4129-46df-b562-f40a2e867103}taskBehaviorTypeId',
                operator: "not in",
                value: {cons!CMGT_REFID_TASK_BEHAVIOR_TYPE_AUTOMATION,cons!CMGT_REFID_TASK_BEHAVIOR_TYPE_AUTOMATION_SEND_JSONRESPONSE}
              ),

Also, as you have some common filters in both the logicalExpression, you can try moving the common block with 'AND' in one logical expression and the rest of the filters within another logicalExpression having 'OR' Operator. Something like below

a!queryLogicalExpression(
  operator: "AND",
  /*Common Filters*/
  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}',
      operator: "=",
      value: true
    ),
    a!queryFilter(
      field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{53258638-2309-421d-a788-6010821afd95}',
      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}',
      operator: "=",
      value: today()
    ),
    a!queryFilter(
      field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{63875207-4129-46df-b562-f40a2e867103}taskBehaviorTypeId',
      operator: "not in",
      value: {
        cons!CMGT_REFID_TASK_BEHAVIOR_TYPE_AUTOMATION,
        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
    )
  },
  logicalExpressions: a!queryLogicalExpression(
    operator: "OR",
    filters: {
      a!queryFilter(
        field: 'recordType!{ee5aa46d-0228-430f-a794-1134a11fbcc0}CMGT_Task.fields.{fb774270-07a3-4a04-93a1-110b11b22d61}isCompleted',
        operator: "=",
        value: false()
      ),

    },
    /*Conditional Filters*/
    logicalExpressions: a!queryLogicalExpression(
      operator: "AND",
      filters: {
        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}',
          operator: "=",
          value: today()
        )
      }
    )
  )
)

Hope it helps!

Shubham Aware · Answer

Made small improvements to your code for better optimal solution. 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(
 operator: "AND",
 filters: {
 /* Common filters for both completed and pending */
 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: "not in",
 value: {
 cons!CMGT_REFID_TASK_BEHAVIOR_TYPE_AUTOMATION,
 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()
 )
 },
 logicalExpressions: a!queryLogicalExpression(
 operator: "OR",
 logicalExpressions: {
 /* Pending/Open tasks */
 a!queryLogicalExpression(
 operator: "AND",
 filters: {
 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()
 )
 }
 ),
 /* Completed today tasks */
 a!queryLogicalExpression(
 operator: "AND",
 filters: {
 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
 ),
 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",
 {}
 ),
 /* Rest of your UI code remains the same */
 ...
)