# PRE\_FILTER not working on sumOver(distinct\_countif())

**URL:** <https://community.amazonquick.com/t/pre-filter-not-working-on-sumover-distinct-countif/27873>\
**Category:** Q&A\
**Tags:** level-aware, calculations, error, Business-Intelligence-Engineer, quick-sight\
**Created:** [March 28, 2024, 9:34am UTC](https://community.amazonquick.com/t/pre-filter-not-working-on-sumover-distinct-countif/27873 "2024-03-28T09:34:39Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![chanQS](https://avatars.discourse-cdn.com/v4/letter/c/cab0a1/32.png) [@chanQS](https://community.amazonquick.com/u/chanQS)\
**Post date:** [March 28, 2024, 9:34am UTC](https://community.amazonquick.com/t/pre-filter-not-working-on-sumover-distinct-countif/27873/1 "2024-03-28T09:34:39Z")

</div>

Im having trouble using the PRE\_FILTER parameter. the calculations are below.

calc1  
`sumOver(distinct_countIf({customer_id}, {date} >= {curr_month_start_date}), [], PRE_FILTER)`

calcl2  
`sumOver(sumif({total_amount}, {date} >= {curr_month_start_date}), [], PRE_FILTER)`

Context: I have other columns that might be used for filtering like region. What I want is even if the region is filtered, I still want the calculation to be computed based on the total sum of the whole dataset for that column.

However, Im having this error:  
For calculation levels PRE\_FILTER and PRE\_AGG, the operands can’t be aggregated.

---

<div class="post-metadata">

**Author:** ![imaitri](https://avatars.discourse-cdn.com/v4/letter/i/47e85d/32.png) [@imaitri](https://community.amazonquick.com/u/imaitri)\
**Post date:** [March 28, 2024, 3:25pm UTC](https://community.amazonquick.com/t/pre-filter-not-working-on-sumover-distinct-countif/27873/2 "2024-03-28T15:25:08Z")

</div>

Hi chanQS,

It the throwing the “For calculation levels PRE\_FILTER and PRE\_AGG, the operands can’t be aggregated.” error because within the sumover() function, you are using the aggregation function such as sum and count

Can you try following for cal1:  
distinctCountOver(ifelse({date} \>= {curr\_month\_start\_date}, {customer\_id}, NULL), , PRE\_FILTER)

and following for cal2:  
sumOver(ifelse({{date} \>= {curr\_month\_start\_date}, {total\_amount}, 0), , PRE\_FILTER)

---

<div class="post-metadata">

**Author:** ![chanQS](https://avatars.discourse-cdn.com/v4/letter/c/cab0a1/32.png) [@chanQS](https://community.amazonquick.com/u/chanQS)\
**Post date:** [March 28, 2024, 10:13pm UTC](https://community.amazonquick.com/t/pre-filter-not-working-on-sumover-distinct-countif/27873/3 "2024-03-28T22:13:46Z")

</div>

I tried that but the error now is:  
PRE\_AGG\_CALCULATION\_LEVEL\_MISMATCH

I noticed that when I add it to the visual, its using (Min) instead of custom.

![image](https://d1wl4jav5dp96l.cloudfront.net/original/3X/a/4/a48e7108d59b4b7895c0dad563dd10d78bdfd5f4.png)

---

<div class="post-metadata">

**Author:** ![WLS-D](https://d2orw1595917va.cloudfront.net/user_avatar/community.amazonquick.com/wls-d/32/36109_2.png) [@WLS-D](https://community.amazonquick.com/u/WLS-D)\
**Post date:** [May 23, 2024, 4:01pm UTC](https://community.amazonquick.com/t/pre-filter-not-working-on-sumover-distinct-countif/27873/4 "2024-05-23T16:01:52Z")

</div>

Hi @chanQS,

There are 2 important things to note on this. First of all, the LAC-W aggregation will by default display as a min aggregation in the field well because the nature of the function will return the same value on multiple rows. You only want to return a single instance of the value to ensure you are getting the correct response. Max, Min, or Median would all return the same thing, and you would want to avoid Sum or Avg.

As for the PRE\_AGG\_CALCULATION\_LEVEL\_MISMATCH error, is one of the fields you are using within the sumOver or distinctCountOver calculations another LAC-W aggregated field? It looks like you are trying to nest a function on the PRE\_AGG level within a function on the PRE\_FILTER level. This will not function correctly, and both calculations need to match for this to work.

I will mark my response as the solution, but please let me know if you need any further guidance. Thank you!
