Grouping and frequency

[jochendecraene]

Hello

I have this dataset with unique cliënts that stayed in our shelter. Every night a person stays with us his of her unique number appears in the dataset. Now, I want to know how many times a person has stayed with us. That works fine, I do a distincount on the unique numbers and a count for the total (see picture). So I see how many times a numer appears. 

But know I want to categorise/group/bin them into to categories, f.e. >1, >5, >10 nights; so I can see how many people stayed for mor than 5 nights. I did some research and tried to use "SWTICH(TRUE))" but I get a "1" time for every id ... Can some help me? 

[amitchandak]

@jochendecraene , Not very clear. You need to create a bucket total at the client id level, using an independent table.

You can create a dynamic segmentation https://www.youtube.com/watch?v=CuczXPj0N-k

[jochendecraene]

@amitchandak 

thnx for the quick response

i watched the video but I'm stuck in the beginning. I'll try to explain it more clearely in the image below.

[amitchandak · Accepted solution]

@jochendecraene , You have to create a table, you do not need this Switch. Table will have ranges like 

0 2 0-2

3 5 <=5

Please find the attached file for the video after signature 

Can you share sample data and sample output in a table format? Or a sample pbix after removing sensitive data.

[jochendecraene]

@amitchandak 

 Thank you for the help. 

I'm not allowed to upload a pbix file in my profile, so here's a link to my one drive  

https://cawovl-my.sharepoint.com/:u:/g/personal/jochendecraene_cawoostvlaanderen_be/EZrIdVqUpmxFouzh... 

The dataset goes over more than 1 year, so I should be able to filter by year

[jochendecraene]

Thnx!!!

the dynamic segmentation worked. I forgot to summarize my ID's ... so I had nothing to segement 🙄