Forum Discussion

leo82's avatar
leo82
Cloud Apps
06-24-2022
Solved

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

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
--------------------------------|-----------------------

dashboards & reporting
data models
  • Konrad's avatar
    Konrad
    06-30-2022

    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 

1 Reply

Replies have been turned off for this discussion
  • Konrad's avatar
    Konrad
    Cloud Apps
    06-30-2022

    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