Grouping Over Values (Cutoff Values)

[abhaysingh]

Hi Guys,

I have one report , customer wise outstanding..

so if i have 100 customers than all may have outstanding amount, more or less..

what i want is below a particular cutoff value i want to see the grouping.

Say below 50000 the customer outstanding amount should show as total else above 50000 should show as line item wise

say input  like this

Cust	Value
a	60000
b	65000
c	70000
d	100000
e	51000
f	10000
r	5000
g	20000
t	15000
y	10000

Output shud be like this

Cust	Value
a	60000
b	65000
c	70000
d	100000
e	51000
Below 50000 Customers	60000

Hope i m clear with output i required

Regards

Abhay

[#2]

Hi Abhay

I used the following expression to create a calculated dimension. The expression will create the new 'bucket' you want to create for customers below 50000

=if(aggr(sum(Value),Cust) > 50000, Cust, 'Below 50000 Customers')

[ajsjoshua]

Hi,

PFA

choose cust and see their cutoff(grade)

[#4]

Try like below,

Load

*,

if(Value<50000,'Below 50000',Cust) as Customer

resident (Main Source table);

[Kushal_Chawda]

Data:

LOAD  Cust,

            Sum(Value) as Value

FROM Table

Group by Cust;

New:

noconcatenate

LOAD Cust,

          Value

Resident Data

where Value>50000;

Concatenate(New)

LOAD 'Below 50000' as Cust,

           sum(Value) as Value

Resident Data

where Value<50000;

Drop table Data;

If you have Other columns As well, link this table to original Table on Cust

[sunny_talwar]

Or this for a way to let the limit being decided on a front end using user input

Data:

LOAD Cust,

    Value

FROM

[https://community.qlik.com/thread/215013]

(html, codepage is 1252, embedded labels, table is @1);

New:

NoConcatenate

LOAD Cust,

    Value

Resident Data

where Value > $(vLimit);

Concatenate(New)

LOAD 'Below ' & $(vLimit) as Cust,

    Sum(Value) as Value

Resident Data

where Value < $(vLimit);

DROP table Data;