Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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....