I’m having a problem with segmentation and the count or countover function as well.
So this is what I’m having, I already created a calculated field that segments app_user_id, but I’m having trouble with the visualization of the data, because what I want to show is something like
Segments | Count_users
more than 50 | 100
Less than 50 more than 25 | 25
Etc.
And after doing that I want to show the results in a pie_chart, I did it my way in SQL but the results can’t be filtered by Date, so I’m recurring to you guys.
Any help to push me in the right direction is much appreciated.
In the Pie_chart I want to display the different segments, like 30% is above 50, 25% is above 25-less than 50, so yeah I think, the 148+102+79+63+54+51 as a whole
What happens if you just put Segments in the “Group/Color” field well and app_user_id (Count) in the “Value” field well? Unless I’m misunderstanding your requirement, I feel like that should give you what you’re looking for.
it says “Custom Aggregation fields are not allowed as a dimension” So I can’t put the segments in the group/color field, and my SQL query is this:
SELECT
segmento,
created_At,
COUNT(*) AS cantidad
FROM (
SELECT app_user_id,
CASE
WHEN count(*) > 50 THEN 'Más de 50'
WHEN count(*) >= 25 AND count(*) <= 50 THEN 'Entre 25 y 50'
WHEN count(*) >= 10 AND count(*) < 25 THEN 'Entre 10 y 25'
WHEN count(*) >= 5 AND count(*) < 10 THEN 'Entre 5 y 10'
ELSE 'Menor a 5'
END AS segmento,
created_At
FROM scanning_history
GROUP BY app_user_id, created_At
) AS subconsulta
WHERE created_At >= '2022-01-01 00:00:00'
GROUP BY segmento, created_At
I’m having these problem from last week, I read all the documentation but it is pretty difficult to apply it with no real world troubleshoot
OK, it’s clearer now. I thought that Segment was based on age or something, and didn’t realize it was calculated.
Can you try this?
Count of app_user_id = countOver({app_user_id}, , PRE_AGG)
Segment =
ifelse(
{Count of app_user_id} > 50, ‘Más de 50’,
{Count of app_user_id} >= 25 AND {Count of app_user_id} <= 50, ‘Entre 25 y 50’,
{Count of app_user_id} >= 10 AND {Count of app_user_id} < 25, ‘Entre 10 y 25’,
{Count of app_user_id} >= 5 AND {Count of app_user_id} < 10, ‘Entre 5 y 10’,
‘Menor a 5’
)
Then put Segment in the “Group/Color” field well and Count of app_user_id(Min) in the “Value” field well.
Why do you group by created_At here? If created_At is at the second level, doesn’t this mean you’re counting how many times a user scans in the same second?
I’m sorry David, I posted a wrong SQL query, this is the real code:
SELECT
segmento,
COUNT(*) AS cantidad
FROM (
SELECT app_user_id,
CASE
WHEN count(*) > 50 THEN 'Más de 50'
WHEN count(*) >= 25 AND count(*) <= 50 THEN 'Entre 25 y 50'
WHEN count(*) >= 10 AND count(*) < 25 THEN 'Entre 10 y 25'
WHEN count(*) >= 5 AND count(*) < 10 THEN 'Entre 5 y 10'
ELSE 'Menor a 5'
END AS segmento
FROM scanning_history
GROUP BY app_user_id
) AS subconsulta
GROUP BY segmento
That’s the nearest I’ve been of an answer to this case, but again, I need to filter through dates in the dashboard, So if the pie chart doesn’t move or take any action is not gonna be accurate
Scans per User = countOver({created_at}, [{app_user_id}], PRE_AGG)
Segment =
ifelse(
{Scans per User} > 50, ‘Más de 50’,
{Scans per User} >= 25 AND {Count of app_user_id} <= 50, ‘Entre 25 y 50’,
{Scans per User} >= 10 AND {Count of app_user_id} < 25, ‘Entre 10 y 25’,
{Scans per User} >= 5 AND {Count of app_user_id} < 10, ‘Entre 5 y 10’,
‘Menor a 5’
Then put Segment in the “Group/Color” field well and app_user_id(distinct count) in the “Value” field well.
Glad I could help! In general, if you need to use the calculated field as a filter or as a dimension in your visual, use countOver instead of count, sumOver instead of sum, etc.
