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.

I need to create a pie chart based on below scenario

Top Replies