topic Re: Grouping Expression Data by a range in QlikView

Grouping Expression Data by a range

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Grouping Expression Data by a range

sunny_talwar — Wed, 04 Apr 2018 15:54:43 GMT

Check here

Buckets

Re: Grouping Expression Data by a range

Anonymous — Wed, 04 Apr 2018 16:04:35 GMT

Will this work even if i want to keep the original value also?

Example of an expression - =sum((TOTAL_USAGE)/12)/(-ONHAND_QTY)

I have around 6 of these 'buckets' which will need to be created, all with different expressions but some of the same variables.

Re: Grouping Expression Data by a range

sunny_talwar — Wed, 04 Apr 2018 16:05:49 GMT

You want to keep original values? Not sure I follow? Would you be able to share a sample?

Re: Grouping Expression Data by a range

Anonymous — Thu, 05 Apr 2018 08:36:26 GMT

Hi Sunny,

So sorry for the late response. I have attached a sample file with a quite rubbish attempt to explain what I am looking for. Would you be able to have a look?

Thanks !

Re: Grouping Expression Data by a range

sasiparupudi1 — Thu, 05 Apr 2018 08:50:52 GMT

May be like this?

Re: Grouping Expression Data by a range

sunny_talwar — Thu, 05 Apr 2018 08:52:58 GMT

Something like this?

=If(Sum(COST)*(Total_Usage) < 0, '<0',

If(Sum(COST)*(Total_Usage) = 0, '0',

If(Sum(COST)*(Total_Usage) < 5, '<5',

If(Sum(COST)*(Total_Usage) < 10, '<10',

If(Sum(COST)*(Total_Usage) < 15, '<15',

If(Sum(COST)*(Total_Usage) < 20, '<20',

If(Sum(COST)*(Total_Usage) < 25, '<25',

If(Sum(COST)*(Total_Usage) < 30, '<30',

If(Sum(COST)*(Total_Usage) < 40, '<40',

If(Sum(COST)*(Total_Usage) < 50, '<50',

If(Sum(COST)*(Total_Usage) < 100, '<100',

If(Sum(COST)*(Total_Usage) < 150, '<150',

If(Sum(COST)*(Total_Usage) < 200, '<200',

If(Sum(COST)*(Total_Usage) < 250, '<250',

If(Sum(COST)*(Total_Usage) < 500, '<500',

If(Sum(COST)*(Total_Usage) < 750, '<750',

If(Sum(COST)*(Total_Usage) < 1000, '<1000',

If(Sum(COST)*(Total_Usage) < 1500, '<1500', '>1500'))))))))))))))))))

Re: Grouping Expression Data by a range

Anonymous — Thu, 05 Apr 2018 09:03:35 GMT

This is what I am looking for, however instead of it as another expression in my table, I would like it as a selection box such as this in excel.

Even as a selection in say a list box would work.

Re: Grouping Expression Data by a range

sunny_talwar — Thu, 05 Apr 2018 09:10:16 GMT

Something like this?

Re: Grouping Expression Data by a range

Anonymous — Thu, 05 Apr 2018 09:19:05 GMT

That is exactly what I am looking for. I have done the below for my expression, but it is returning null. Can you see what is wrong?

=Aggr(

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Re: Grouping Expression Data by a range

sunny_talwar — Thu, 05 Apr 2018 11:12:03 GMT

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

What is it doing?

Re: Grouping Expression Data by a range

Anonymous — Thu, 05 Apr 2018 11:28:54 GMT

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

Re: Grouping Expression Data by a range

sunny_talwar — Thu, 05 Apr 2018 11:29:52 GMT

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

Re: Grouping Expression Data by a range

Anonymous — Thu, 05 Apr 2018 13:37:40 GMT

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

Re: Grouping Expression Data by a range

sunny_talwar — Thu, 05 Apr 2018 13:43:03 GMT

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

Re: Grouping Expression Data by a range

Anonymous — Thu, 05 Apr 2018 14:24:28 GMT

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

Re: Grouping Expression Data by a range

sunny_talwar — Thu, 05 Apr 2018 14:26:51 GMT

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

Re: Grouping Expression Data by a range

Anonymous — Thu, 05 Apr 2018 14:47:26 GMT

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)

Re: Grouping Expression Data by a range

sunny_talwar — Thu, 05 Apr 2018 14:53:32 GMT

These are the only things which look off

Re: Grouping Expression Data by a range

Anonymous — Thu, 05 Apr 2018 15:02:11 GMT

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