Grouping Sales together

[McintoschRab]

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

[MatheusC]

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

[rwunderlich]

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]

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]

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

[McintoschRab]

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

[marcus_sommer]

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.  

[rwunderlich · Accepted solution]

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