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!
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 https://www.youtube.com/watch?v=RSZe1AsRt2Y) 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.
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.
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.
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.
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 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%.
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.
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)
@David_Wong 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:
Now I cannot add the link or embed the image to this comment.
