How can I use the PRE_FILTER for the sumOver function?

Hi guys, I have an issue when using the PRE_FILTER with the function sumOver() for the calculated field "# users by registered date" . Here is the custom code I’ve used, which does not have any problem:
sumOver({# users},[RegisteredDate]).

However, when I’m trying to filter my dashboard by the calculated field “isFTB” = 1 (where the FTBDate is not null) and I don’t want this filter to affect my calculated field "# users by registered date", I’ve tried to use the argument PRE_FILTER for it like:
sumOver({# users},[RegisteredDate],PRE_FILTER)

However, it turns the error “For calculation levels PRE_FILTER and PRE_AGG, the operands can’t be aggregated.” like the image in this link (sr I cannot embed it here): Imgur: The magic of the Internet

The reason I want to do those steps above is that I don’t want this null column like the image (Imgur: The magic of the Internet). This null column contains the value of the records which have the FTBDate as null.

How can I fix this problem? I’ve tried to use the functions countOver(), sum() or count() but it doesn’t give me the result I want. Thank you so much.

@longpham9999 - Thank you for posting your query. I believe you should be using countOver only with RegisteredDate and the Other Dimension (present in the Pivot Table Row) both included in the calculation partition. That will give you the numbers at both RegisteredDate and that dimension. Otherwise only using countOver with Registered Date will eliminate the users who are overlapping across the Row Dimension which will minimize your numbers. This is my understanding from the snippet of data that I was able to see. A bit more details on the underlying dataset would help to re-create the problem. Thank you!

1 Like

Hi sagmukhe,

Thank you so much for your reply. I have tried what you recommended, but it still doesn’t make sense.

All I need to do is try to perform a cohort analysis (like the AWS QuickSight’s tutorial How to Perform Cohort Analysis - YouTube) for users who have registered for the app and then become a first-time buyers (means those who have FTB Date is not null). As far as I know, to calculate like that, I need to have a formula A/B, when A is the number of users who have registered the app and have FTB Date is not null and B is the total number of users who have registered the app and have FTB date (null is also counted). B in this case is the calculated field " # users by registered date".

That’s why I tried to use the argument “PRE_FILTER” for B because I don’t want my filter “FTB date is not null” will affect it.

The column will be the FTB Date which is grouped by Month/Year (like March 2022) and the rows will be grouped by RegistratedDate which is grouped by Month/Year (like March 2022).

Cohort analysis is usually used to look at groups of users and analyze their usage pattern over time. In your case the users would be grouped by their registration date, e.g. you want to compare users who registered in Feb 2022, March 2022, etc.

If you want A to be the number of users who have registered and become first-time buyers, I’m not sure a cohort analysis would make sense because they would become first-time buyers just once. There would not be any change in behaviour to analyze over time.

If you still want to try:
A = countOver({# users},[RegisteredDate], PRE_AGG)
B = countOver({# users},[RegisteredDate], PRE_FILTER)
Create another field to divide A by B and filter your visual by isFTB = 1.

1 Like

Hi David,

Thank you so much for your reply. Unfortunately, I’ve tried your code for the calculated field B but it still says “For calculation levels PRE_FILTER and PRE_AGG, the operands can’t be aggregated.” I really don’t know what is happening.

@longpham9999 Is # users a calculated field? If so, can you show the calculation?

1 Like

Hi, I will give you all the calculated fields that I tend to use for my visualization:

{IsFTB} = isNotNull(FTBDate)
A = countIf ( UserId, {IsFTB} = 1 )
# users = count(UserId)
B = countOver({# users},[RegisteredDate], PRE_FILTER)
The final formula will be: {A} / {B}

However, whenever I use the function sumOver or the function countOver with the argument PRE_FILTER, it still throws me the error " “For calculation levels PRE_FILTER and PRE_AGG, the operands can’t be aggregated.”

I used the pivot table: when the row is the RegisteredDate which is grouped by Month/Year (like March 2022) and the column is the FTBDate which is grouped by Month/Year (like May 2022). I don’t know whether it will affect the argument PRE_FILTER.

All I want is that the filter for the FTBDate I use later cannot affect the calculated field B

You don’t need the {# users} field because B is already doing a count. That’s what the error message "For calculation levels PRE_FILTER and PRE_AGG, the operands can’t be aggregated” is saying. The operand refers to the part before the first comma in your countOver. {# users} is already an aggregation, so you can’t use it in countOver.

Try this:
First-time buyers = countOver(UserId, [RegisteredDate], PRE_AGG)
Registered users = countOver(UserId, [RegisteredDate], PRE_FILTER)

If you divide first-time buyers by registered users, you’ll get the percentage of registered users who are first-time buyers.

% of first-time buyers = {First-time buyers}/{Registered users}

Create a vertical bar chart with RegisteredDate on the x-axis, and % of first-time buyers on the y-axis. Then filter the visual by isFTB = 1 (this part is very important). If RegisteredDate is at the month level, each bar will represent the percentage of users who became first-time buyers in the same month that they registered.

Thank you so much for your recommendations. Actually, the task requires me to put the RegisteredDate on the Y-axis and group it by Month/Year; and the FTBDate on the X-axis and group it by Month/Year. It says that (each column is the month that the user becomes a first-time buyer).

Do you have any idea about this? I’ve tried what you said but it doesn’t look like a cohort as much. You can see it via this link: Imgur: The magic of the Internet

OK, that makes sense. Then, you just have to replace RegisteredDate by FTBDate in the first calculated field and I think you don’t need to filter your visual by isFTB = 1 anymore.

First-time buyers = countOver(UserId, [FTBDate], PRE_AGG)

The remaining calculations should still be the same. After you have all your calculated fields, you can create your visual like in this demo:
https://democentral.learnquicksight.online/#Dashboard-FeatureDemo-Custom-Visual-Cohort-Analysis

Btw you don’t need to upload your screenshots to an external site and link to it. You can copy and paste them in your post like this:

1 Like

Thank you so much. After several tries, I think I still need a filter isFTB = 1 because if I don’t add it, it will have a null column like this (the second image):

And after I add the filter isFTB = 1, it will exclude this null column from the visualization :frowning: Now I cannot add the link or embed the image to this comment.

However, I still don’t understand why the overall calculated field doesn’t look like I expect. If I convert it from number to percentage, it will be weird, for example, 3 will be 300%.

Here are my calculated fields after updating:

First-time buyers = countOver(UserId, [FTBDate], PRE_AGG)
Registered users 2 = countOver(UserId, [RegisteredDate], PRE_FILTER)
% of first-time buyers = {First-time buyers}/{Registered users 2}

And I have one more question, if I have to add the condition for first-time buyers, for example, for only Golden members, can I only apply the filter for only this calculated field? Because I’ve tried the countIf() function inside the countOver() function with the argument PRE_FILTER or PRE_AGG, it still throws me the same error that I mentioned previously.

If you change this sum to min, I think you’ll get the right result.

The null column represents the users who have still not bought anything yet. If you don’t need it, it’s OK to filter by isFTB = 1 to exclude it.

Do you want to count only golden members in First-time Buyers but all users in Registered Users? If so, you can just add the filter to the visual. The PRE_FILTER that you used to calculate Registered Users will ensure that it is not affected by the filter.

If you want to count only golden members both in First-time Buyers and in Registered Users, change your calculation for Registered Users as follows and then filter by the visual by the field that identifies the golden members.
Registered Users = countOver(UserId, [RegisteredDate, isGolden], PRE_FILTER)

1 Like

Thank you so much for your recommendation. Unfortunately, if I change this sum to min, the result will not be correct anymore. I have tried it.

All I need now is that I can have a result like this image. I can exclude the null column and use the filter without affecting the result.

For more details about what I mean, you can check the image below:

Here is the image for the result I want:
I don’t know why I cannot include the Imgur link in the same comment.