In Sisense, how can you "group by" in ranges?

[leo82]

Suppose I have data organized in a pivot (widget). where within the pivot table, each rows represent a team member, e.g. (jack, jon, doe etc). The team members are part of teams, where 'team name' is a column in the same pivot (widget)

I'd like to generate a table (widget) of counts, that shows how many team members per team that appear in each range. Can someone please guide me in the right direction. Many Thanks!

For example: outcome should look like below.
---------------------------------|----------------------
0-5 member per team | 5 total teams
---------------------------------|-----------------------
5-8 member per team | 3 total teams
--------------------------------|-----------------------
8-12 member per team | 6 total teams
--------------------------------|-----------------------

[Konrad · Accepted solution]

Hi @leo82 ,
I would do this on Elasticube level. It depends how your data structure looks like and what are the table names, but you can try implementing following logic:

First I would create custom table, let's call it team_size_count. It will calculate the number of users in each group with following query:

select a.[Team_id], case when count(a.[uesr_ID]) <= 5 then '0-5 member per team'
when count(a.[user_ID]) > 5 and count(a.[user_ID]) <= 8 then '6-8 member per team'
when count(a.[user_ID]) > 8 and count(a.[user_ID]) <= 12 then '9-12 member per team'
end as Team_Size
from [Users] a
group by a.[Team_id]

Then you can create a team_size custom column on Teams table with following query:

lookup([Team_Size],[Team_Size_Count],[id],[team_id])

In next step you can achieve desired view by using this column in pivot table widget in rows section and as value simply count the number of teams

--
Konrad Wróblewski
QBeeQ