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]

Everything seems to be below 0... try this

=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)) <1, Dual('0-1', 3),

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

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

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

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

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

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

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

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

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

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

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

Some notes

1) QlikView and Qlik Sense are case sensitive... %KEY1 is different then %Key1

2) If with and condition needs the condition to be specified twice

If(Condition > 0 and <1 is not right... you need this If(Condition > 0 and Condition < 1 is the right way to do... but in your case... I don't think you need the first part....