topic Re: Grouping Over Values (Cutoff Values) in QlikView

Grouping Over Values (Cutoff Values)

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

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

Re: Grouping Over Values (Cutoff Values)

Fri, 29 Apr 2016 04:51:49 GMT

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')

Re: Grouping Over Values (Cutoff Values)

ajsjoshua — Fri, 29 Apr 2016 04:59:38 GMT

Hi,

PFA

choose cust and see their cutoff(grade)

Re: Grouping Over Values (Cutoff Values)

Fri, 29 Apr 2016 05:10:05 GMT

Try like below,

Load

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

resident (Main Source table);

Re: Grouping Over Values (Cutoff Values)

Kushal_Chawda — Fri, 29 Apr 2016 06:07:33 GMT

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

Re: Grouping Over Values (Cutoff Values)

sunny_talwar — Fri, 29 Apr 2016 06:21:45 GMT

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;