Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Announcements

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Analytics
- :
- Re: Grouping Sales together

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

McintoschRab

Contributor II

2024-03-26
04:04 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Grouping Sales together

Hi,

I've been trying to group some sales together however I can't make it work.

I've gotten this far: (which I found in another Qliksense Community question)

=if(aggr(Sum(sales), [sales rep]) >= 0 and aggr(Sum(sales), [sales rep]) <= 500, 'Group 1', 'Group 2')

This works fine, however I want to create more groups. Right now I get it to successfully group sales reps into the 'group 1' group and the rest of the sales reps go 'group 2'

How would I extend this formula to get more groups? These are the buckets I want:

Group 1: 0 - 500

Group 2: 500 - 1000

Group 3: 1000 - 2000

Group 4: 2000 - 3000

Group 5: 4000+

Thanks,

Rob

652 Views

1 Solution

Accepted Solutions

rwunderlich

Partner Ambassador/MVP

2024-03-27
04:05 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I think you can just continue the progression to include negatives.

if(Aggr(Sum(sales), [sales rep]) > 3000, '3k+'

,if(Aggr(Sum(sales), [sales rep]) > 2000, '2k+'

,if(Aggr(Sum(sales), [sales rep]) > 1000, '1k+'

,if(Aggr(Sum(sales), [sales rep]) > 500, '500+'

,if(Aggr(Sum(sales), [sales rep]) >= 0, '0+'

,if(Aggr(Sum(sales), [sales rep]) > -500, '-500 to 0'

,if(Aggr(Sum(sales), [sales rep]) > -1000, '-501 to -1000'

,'-1000 or less'))))))

7 Replies

MatheusC

Specialist II

2024-03-26
04:31 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Você pode usar ifs aninhados, com uma condição if dentro de outra condição if:

Veja abaixo:

```
if(aggr(Sum(vendas), [representante de vendas]) >= 0 e aggr(Sum(vendas), [representante de vendas]) <=500,'Group1',
if(aggr(Sum(vendas), [representante de vendas]) >=500 e aggr(Sum(vendas), [representante de vendas]) <=1000,'Group2',
if(aggr(Sum(vendas), [representante de vendas]) >=1000 e aggr(Sum(vendas), [representante de vendas]) <=2000,'Group3',
if(aggr(Sum(vendas), [representante de vendas]) >=2000 e aggr(Sum(vendas), [representante de vendas]) <=3000,'Group4','Group5'))))
```

Atenciosamente, Matheus

Did you find a solution to your question? Mark the solution as accepted ✅ and if you found it useful, press the like button!

rwunderlich

Partner Ambassador/MVP

2024-03-26
06:27 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I find it's usually faster and cleaner to test from highest to lowest in a nested if.

if(Aggr(Sum(sales), [sales rep]) > 3000, 'Group 5'

,if(Aggr(Sum(sales), [sales rep]) > 2000, 'Group 4'

,if(Aggr(Sum(sales), [sales rep]) > 1000, 'Group 3'

,if(Aggr(Sum(sales), [sales rep]) > 500, 'Group 2'

,'Group 1'))))

-Rob

JonnyPoole

Former Employee

2024-03-26
11:07 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

One more option to create dynamic buckets of 1000 between 1000 and 4000 and buckets of 500 between 0 and 1000.

if( aggr(Sum(Sales),[Sales Rep]) > 4000,'4000+',

if( aggr(Sum(Sales),[Sales Rep]) > 1000,

replace(class(aggr(Sum(Sales),[Sales Rep]),1000),'<= x <',' - '),

if( aggr(Sum(Sales),[Sales Rep]) > 0,

replace(class(aggr(Sum(Sales),[Sales Rep]),500),'<= x <',' - ')

)))

McintoschRab

Contributor II

2024-03-27
07:29 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Hi,

This worked a treat. Could you please advise how I would include negative sales in this. I tried this:

if(Aggr(Sum(sales), [sales rep]) > 3000, '3k+'

,if(Aggr(Sum(sales), [sales rep]) > 2000, '2k+'

,if(Aggr(Sum(sales), [sales rep]) > 1000, '1k+'

,if(Aggr(Sum(sales), [sales rep]) > 500, '500+'

,if(Aggr(Sum(sales), [sales rep]) >= 0, '0+'

,if(Aggr(Sum(sales), [sales rep]) < 0, '-0'

,if(Aggr(Sum(sales), [sales rep]) < -1000, '-1000'

,'Group 1'))))))

592 Views

McintoschRab

Contributor II

2024-03-27
08:08 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

This actually also worked really well.. even going in negatives.

One remark in general.. it be nice to have these buckets sort properly going from the lowest to the highest buckets.

Been trying to mess around with sorting but can't seem to get it right. Got any idea how to fix that?

Cheers,

Rob

590 Views

marcus_sommer

MVP

2024-03-27
08:34 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

Instead of just assigning a string-value to a value-check you could apply a dual(YourString, YourSorting) and defining there the wanted sort-order.

Beside of this you may consider to provide only equally buckets because then a single aggregation with class(aggr(Expr, Dim), **$(var)**) would be enough and like hinted with the bold variable such approach could be dynamic. Especially by larger data-sets and/or many buckets a nested if-loop with aggr() could become a performance-killer. And even by unequally clusters are ways possible to assign the right bucket with a single aggregation.

571 Views

rwunderlich

Partner Ambassador/MVP

2024-03-27
04:05 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

I think you can just continue the progression to include negatives.

if(Aggr(Sum(sales), [sales rep]) > 3000, '3k+'

,if(Aggr(Sum(sales), [sales rep]) > 2000, '2k+'

,if(Aggr(Sum(sales), [sales rep]) > 1000, '1k+'

,if(Aggr(Sum(sales), [sales rep]) > 500, '500+'

,if(Aggr(Sum(sales), [sales rep]) >= 0, '0+'

,if(Aggr(Sum(sales), [sales rep]) > -500, '-500 to 0'

,if(Aggr(Sum(sales), [sales rep]) > -1000, '-501 to -1000'

,'-1000 or less'))))))

Community Browser