I just incurred in an issue with QS for something which I thought was straightforward and I’d like to understand if I am doing something wrong here.
TLDR: I would like to use a visual on top which simply return the count of hits I will get in the main table. This is done by a distinct count of the user IDs. The issue is that the filters based on measures do not get applied to this visual.
Long version.
Let’s say that my initial data is something like this:
UserID
Date
Gender
Purchases
Sales
123
01/01/2023
Male
€0
€100
345
01/01/2023
Female
€100
€200
123
02/01/2023
Male
€50
€100
123
03/01/2023
Male
€50
€50
345
03/01/2023
Female
€200
€0
345
04/01/2023
Female
€150
€500
Very straightforward: aggregated daily data with some user information.
My dashboard is just a table with a few filters on top.
I can select a date range and I will get the total amount of purchases and sales in that period, plus I can filter on gender and (this is the core of the issue), the minimum purchases or sales amount.
Say I can select the Date from 01/01/23 to 03/01/23 and I would get something like this:
UserID
Gender
Purchases
Sales
123
Male
€100
€250
345
Female
€300
€200
I want then to create another visual with the distinct count of users returned here.
What I do is adding a KPI visual with the distinct count of userID applying the same filters. So I should simply get “2” in this case.
The issue now is with the filter based on the measures.
I create one more filter based on “Purchases”, and I select:
Aggregation: Sum
Filter Condition: Greater Than or Equal To -1 (this way by default it’ll always return all users)
NULL option: exclude
I add it to the sheet and pin it on top using the setting to use a text field here.
Keeping the previous filters, I then select “250”. My expectation is that it’ll show me only clients whose total purchases were at least €250 in the time window I specified.
My main table gets updated:
UserID
Gender
Purchases
Sales
345
Female
€300
€200
My new visual with the users count, does not.
The filter does not seem to have an effect.
Please note that:
The filter is correctly applied to the visual
When I use it, I can see the visual being recalculated, but then the result does not change
Am I doing something wrong here?
Any reason why the count is not being updated properly?
I am guessing that I am missing something really small and trivial because this is not a complicated task, but I cannot figure out what.
First of all, thank you for helping me!
Yes, the KPI keeps showing the original number, in my example 2.
The filter has simply no effect so it shows all users, after the other filters have been applied. I also tried duplicating my original table, removing all fields, turning it into a KPI and adding the distinct count of users (to make sure the starting filters and conditions were the same), but still the same.
Following your suggestion, I once again created a KPI visual, added the count of distinct users, then checked that all filters were applied.
I then duplicated it and changed the KPI into a table, moving the userID in the group by and saved.
When I apply any filters which are not based on aggregated measures, everything gets updated accordingly. But once I apply one filter based on these measures, this not get applied.
Not sure why the grouping here matters: I am selecting the distinct count of userIDs so even if in the time window I select the userID appears ten times, it should be counted only once.
I also tried another experiment: I created a bogus calculated field whose only value is “matches”, then used the table to group by this field and again count the distinct occurrences of userIDs, but nothing changed.
Am I misunderstanding something on how the logic works?
Thank you again for your time, it is much appreciated.
Sorry, I am not sure I am following you.
Each customer has a unique UserID.
So in your example, the amount would not get summed and become $300.
It would be still showing two users, 123 and 345, and the distinct count of UserIDs would be two. But if I also applied a filter on the amount, say “$200” to the aggregated sum, then while the table would only show the user matching this minimum, the distinct count would still return two.
I could look into the average but the point is that I need the distinct count of users whose total amount is X, and at the moment QuickSight is not providing that and I have no clue why.
And this seems such a standard scenario that I cannot wrap my head around it.
Sorry, I am not sure I do understand.
As of now, my table with amount sum seems to be working fine.
I did not find any duplicates or anything, it only shows the individual users and the total purchases / sales amounts for that period.
Please note that I DO have sumOver functions in the table but I do not see how these should be related to my issue. My sumOver functions are basically like the one you mentioned, to calculate the lifetime purchases etc. per user. Something like this:
UserID
Gender
Purchases
Sales
Lifetime Purchases
Lifetime Sales
123
Male
€100
€250
€50
€250
345
Female
€300
€200
€450
€700
So I can show both how much a user spends in a certain time window, but also how much they spent in their lifetime.
But again, the problem is with the distinct_count function. Not sure if this is because it is an aggregated function but it does not make sense to me that I do not have a way to calculate the distinct count of users whose amount sum hit my filters thresholds.
It is like an SQL function in which DISTINCT COUNT only takes into account the WHERE clauses but not HAVING.
At this point I am getting the feeling that I am not doing anything wrong but it’s a hard limit of QuickSight, something I was not expecting since, to me, it’s pretty basic asking the tools to answer questions such as “how many unique records do we have whose total amount sum in this timeframe was at least €X?”.
Apologies if I am misunderstanding and it’s something I am doing wrong.
This will still work even if you are using a calculated field to aggregate UserID like this:
distinct_count(UserID)
and then placing that in the values field well. It will still return 1.
From my experience, the order of operations is very important to understand in order to be successful when building these visualizations.
The date filter applied on the visual will filter out the dates wanting to be excluded in the Sum. So instead of us wanting to see “Life Time Purchases” we are returning all Purchases made between 2023/01/01 and 2023/03/01 for each user, and then filtering out the users who don’t meet the threshold.
If you change the PRE_AGG to PRE_FILTER, the date filter won’t have an effect.
Thank you so much for taking so much time educating me.
I now understand what you mean, but I am not sure I understand why it works this way.
It seems to me that the new sumOver calculation basically does the same exact as sum does in this case, am I correct? I ran a few experiments and I always got the same results. But the difference is that if I use it in my filter, then the distint_count returns what I am after, as long as I keep that in this case the aggregation is the minimum.
I agree that it is important to understand the order of operations here, it just eludes me why sum is not working and this is bugging me.
The reason is that sum by itself doesn’t differentiate between groups on its own. This is determined in part by which visual you select.
When you add it to the KPI, the filter by Sum({Purchases}) is adding all of the purchases together within the dates, in this case to $400. It does not distinguish this by UserID.
Then, the filter you applied was trying to filter where that sum was less than $250, but in all cases it was larger than that ($400 for User 123 and $400 for User 345), so it returned both users.
If you apply the method you used to something like a table and add UserID in the group by, Quicksight now will calculate the Sum({Purchases}) for each UserID, and filter out the ones not meeting $250. That is why you got a distinct count of 1 on the table, but 2 within the KPI.
The reason we needed to use the SumOver calculation is because there is no group by within the KPI, so Quicksight has no way to distinguish how to group the Sum. We had to specify Sum by UserID within SumOver.
