Date Comparison View Calculation Error

Question & Answer
, , , ,
1

Hi QuickSight Community, I am trying to create a view that has enables users to select 2 date ranges to show the numeric and percentage difference for Defects Per Million Opportunities (DPMO) in one table.

I found this link on QS community, and I tried to apply the “Last Month” formula. However, I am getting an error of “Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.”

The DPMO calculated field is done by doing (SUM(pkg_missort_count) / MAX(total_pkg_count)) * 1,000,000. The reason why I am doing a MAX() on the total pkg count is because the pkg missort count is broken up into different missort categories, but the total pkg count processed for the sorter is the same. This is why I use MAX() to avoid multiplying the total pkg count.

I know that the error is due to the SUM and MAX function, is there another way I can get around this?

2

Hi @yipk - youll have to put the ifelse logic that does the ‘last month’ part inside each aggregation like this (or smash together with sumIf and maxIf - same result):

sumIf(pkg_missort_count, dateDiff(date field, now(), ‘MM’)) / maxIf(total_pkg_count, dateDiff(date field, now(), ‘MM’))

3

Hi @Jesse thank you for the response!

For further clarification, the pkg missort count and total pkg count should be partitioned by sort center and date. This changes what is in the formula right?

4

Yes, partitioning by sort center and date will change the formulas. You’ll need to incorporate the partitioning into your calculations to ensure the DPMO is calculated correctly for each sort center and date combination. Here’s how you can adjust the formulas accordingly:

  1. Create Parameters for Date Ranges:
  • Go to the Parameters section in QuickSight.
  • Create two parameters for the start dates (startDate1, startDate2) and two for the end dates (endDate1, endDate2).
  1. Create Control for Parameters:
  • Add controls for these parameters so users can select the date ranges.
  1. Filtered Calculated Fields with Partitioning:
  • Create calculated fields to filter the pkg_missort_count and total_pkg_count based on the selected date ranges and partitioned by sort center and date.

For the first date range:

sumIf(
    {pkg_missort_count},
    {date_field} >= ${startDate1} and {date_field} <= ${endDate1} and {sort_center} = {sort_center}
) / maxIf(
    {total_pkg_count},
    {date_field} >= ${startDate1} and {date_field} <= ${endDate1} and {sort_center} = {sort_center}
) * 1,000,000

For the second date range:

sumIf(
    {pkg_missort_count},
    {date_field} >= ${startDate2} and {date_field} <= ${endDate2} and {sort_center} = {sort_center}
) / maxIf(
    {total_pkg_count},
    {date_field} >= ${startDate2} and {date_field} <= ${endDate2} and {sort_center} = {sort_center}
) * 1,000,000
  1. Create Calculated Fields for DPMO with Partitioning:
  • Create calculated fields for DPMO for each date range, partitioned by sort center and date.

For the first date range:

ifelse(
    {date_field} >= ${startDate1} and {date_field} <= ${endDate1} and {sort_center} = {sort_center},
    (sum({pkg_missort_count}) over (partition by {sort_center}, {date_field}) / max({total_pkg_count}) over (partition by {sort_center}, {date_field})) * 1,000,000,
    NULL
)

For the second date range:

ifelse(
    {date_field} >= ${startDate2} and {date_field} <= ${endDate2} and {sort_center} = {sort_center},
    (sum({pkg_missort_count}) over (partition by {sort_center}, {date_field}) / max({total_pkg_count}) over (partition by {sort_center}, {date_field})) * 1,000,000,
    NULL
)
  1. Create a Table Visual:
  • Add a new table visual to your analysis.
  • Add the calculated fields for DPMO to this table.
  1. Calculate the Difference and Percentage Change with Partitioning:
  • Create new calculated fields for numeric and percentage differences between the two DPMO values, partitioned by sort center.

Numeric difference:

sum(
    ifelse(
        {date_field} >= ${startDate1} and {date_field} <= ${endDate1} and {sort_center} = {sort_center},
        (sum({pkg_missort_count}) over (partition by {sort_center}, {date_field}) / max({total_pkg_count}) over (partition by {sort_center}, {date_field})) * 1,000,000,
        NULL
    )
) - sum(
    ifelse(
        {date_field} >= ${startDate2} and {date_field} <= ${endDate2} and {sort_center} = {sort_center},
        (sum({pkg_missort_count}) over (partition by {sort_center}, {date_field}) / max({total_pkg_count}) over (partition by {sort_center}, {date_field})) * 1,000,000,
        NULL
    )
)

Percentage difference:

(sum(
    ifelse(
        {date_field} >= ${startDate1} and {date_field} <= ${endDate1} and {sort_center} = {sort_center},
        (sum({pkg_missort_count}) over (partition by {sort_center}, {date_field}) / max({total_pkg_count}) over (partition by {sort_center}, {date_field})) * 1,000,000,
        NULL
    )
) - sum(
    ifelse(
        {date_field} >= ${startDate2} and {date_field} <= ${endDate2} and {sort_center} = {sort_center},
        (sum({pkg_missort_count}) over (partition by {sort_center}, {date_field}) / max({total_pkg_count}) over (partition by {sort_center}, {date_field})) * 1,000,000,
        NULL
    )
)) / sum(
    ifelse(
        {date_field} >= ${startDate2} and {date_field} <= ${endDate2} and {sort_center} = {sort_center},
        (sum({pkg_missort_count}) over (partition by {sort_center}, {date_field}) / max({total_pkg_count}) over (partition by {sort_center}, {date_field})) * 1,000,000,
        NULL
    )
) * 100
  1. Add the Difference Fields to the Table:
  • Include these calculated fields in your table to show the numeric and percentage differences.

By following these steps, you can create a view in QuickSight that partitions the calculations by sort center and date, allowing for accurate comparisons of DPMO values across different date ranges.