Grouping Expression Data by a range

[Anonymous]

Hi All,

Wondering whether someone can give some help.

How can I group together data output from an expression into a range.

e.g

expression output below, I would like this to be grouped like so: '0','<1','<2','<3' etc

Thanks.

[sunny_talwar]

The only thing which I am not sure about is this negative sign after {1}

What is it doing?

[Anonymous]

Making it a negative number, same result if I remove it from the expression.

[sunny_talwar]

I don't see anything wrong with the expression... but I don't have visibility into it...

[Anonymous]

I have attached an example, I can get it to display 2 selections but they are not correct

[sunny_talwar]

It seems that nothing really is greater than 5 for the expression that you are using in your condition

[Anonymous]

This is fine and that result is fine. However when i select >70 it give me results when none of them are above 70.

[sunny_talwar]

You were aggregating over TOTAL_USAGE... when you aggregate over %Key1... you will see only one option <5

[Anonymous]

Thank you!!! I believe this has fixed my issue.

I have another 'bucket' to make.

Does the between numbers part make sense? its returning a null result so i assume it is wrong haha.

=Aggr(

If((sum({1}-ONHAND_QTY)/(only({1}TOTAL_USAGE)/12)) < 0, Dual('<0', 1),

If((sum({1}-ONHAND_QTY)/(only({1}TOTAL_USAGE)/12)) = 0, Dual('0', 2),

If((sum({1}-ONHAND_QTY)/(only({1}TOTAL_USAGE)/12)) >0 <1 , Dual('0-1', 3),

If((sum({1}-ONHAND_QTY)/(only({1}TOTAL_USAGE)/12)) >1 <2, Dual('1-2', 4),

If ((sum({1}-ONHAND_QTY)/(only({1}TOTAL_USAGE)/12)) >2 <3, Dual('2-3', 5),

If((sum({1}-ONHAND_QTY)/(only({1}TOTAL_USAGE)/12)) >3 <4, Dual('3-4', 6),

If((sum({1}-ONHAND_QTY)/(only({1}TOTAL_USAGE)/12)) >4 <5, Dual('4-5', 7),

If((sum({1}-ONHAND_QTY)/(only({1}TOTAL_USAGE)/12)) >5 <6, Dual('5-6', 8),

If((sum({1}-ONHAND_QTY)/(only({1}TOTAL_USAGE)/12)) >6 <7, Dual('6-7', 9),

If((sum({1}-ONHAND_QTY)/(only({1}TOTAL_USAGE)/12)) >7 <8, Dual('7-8', 10),

If((sum({1}-ONHAND_QTY)/(only({1}TOTAL_USAGE)/12)) >8 <9, Dual('8-9', 11),

If((sum({1}-ONHAND_QTY)/(only({1}TOTAL_USAGE)/12)) >9 <10, Dual('<9-10', 12),

If((sum({1}-ONHAND_QTY)/(only({1}TOTAL_USAGE)/12)) >10 <11, Dual('10-11', 13),

If((sum({1}-ONHAND_QTY)/(only({1}TOTAL_USAGE)/12)) >11 <12, Dual('11-12', 14), Dual('>12', 15))))))))))))))),%KEY1)

[sunny_talwar]

These are the only things which look off

[Anonymous]

I tried to place an 'and' statement in there but didn't seem to help. Any suggestions?