Pre-Filter Issue

Question & Answer
,
2

Hi @maltiza_ivanova, welcome to the QuickSight Community! To ensure the total number of participants is calculated correctly and remains unaffected by filters on participant attributes, we need to adjust our calculations to ensure the total_number_of_participants correctly aggregates the daily participants without being affected by non-date filters.

Try these steps, you may have to adjust the syntax

  1. Create a calculated field number_of_participants_per_day_static in dataset A:
sumOver(minovery({number_of_participants}, [{meeting_id}], PRE_FILTER) / countOver({number_of_participants}, [{meeting_id}], PRE_FILTER), [year, month, day], PRE_FILTER)
  1. Ensure this field is not affected by non-date filters:
  • Use it in a way that it remains static regardless of participant-level filters.
  1. Calculate total_number_of_participants using this static field:
sum({number_of_participants_per_day_static}, [year, month, day], PRE_AGG)

If the above adjustments still do not fully solve the issue, consider using a custom SQL. For example:

WITH participants_per_meeting AS (
    SELECT 
        meeting_id,
        COUNT(participant_id) AS number_of_participants
    FROM 
        dataset_b
    GROUP BY 
        meeting_id
),
participants_per_day AS (
    SELECT 
        a.meeting_date,
        SUM(p.number_of_participants) AS number_of_participants_per_day
    FROM 
        dataset_a a
    JOIN 
        participants_per_meeting p
    ON 
        a.meeting_id = p.meeting_id
    GROUP BY 
        a.meeting_date
),
total_participants AS (
    SELECT 
        SUM(number_of_participants_per_day) AS total_number_of_participants
    FROM 
        participants_per_day
)
SELECT 
    tp.total_number_of_participants
FROM 
    total_participants tp;

Static reference point in your calculation can be tricky. Here is a good reference : Level-Aware Calculations: Tips and Best Practices

Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!