topic Re: Creating Calculated dimension of a range in QlikView

Creating Calculated dimension of a range

zagzebski — Sat, 22 Aug 2015 20:18:45 GMT

I need to create a calculated dimension. The goal is to sum up Client Amount by Region then put into a range. The range is not in equal increments so I don't believe CLASS can be used. The data:

DATA:
Region	Client	Amount
1	A	1000
1	A	2000
1	B	3000
1	B	4000
2	B	5000
2	B	6000
2	B	7000
2	C	8000
3	A	9000
3	C	10000
3	C	11000
SUMMED BY CLIENT/REGION - then the NEW FIELD IS a RANGE:
Region	Client	Amount	NEW FIELD
1	A	3000	Less than 5000
1	B	7000	5000-15000
2	B	18000	15000-30000
2	C	8000	5000-15000
3	A	9000	5000-15000
3	C	21000	15000-30000

Re: Creating Calculated dimension of a range

petter — Sat, 22 Aug 2015 22:26:23 GMT

This is a simple, straightforward solution that should do what you expect:

LOAD

Region,

Client,

Sum(Amount) AS Amount,

If( Sum(Amount) < 5000 , 'Less Than 5000'

, If ( Sum(Amount) < 15000 , '5000-15000'

, If ( Sum(Amount) < 30000 , '15000-30000'

, '>=30000' ))) AS [NEW FIELD]

GROUP BY

Region, Client;

LOAD Region,

Client,

Amount

FROM

[https://community.qlik.com/message/850179?et=watches.email.thread#850179]

(html, utf8, embedded labels, table is @1, filters(

Remove(Row, RowCnd(Interval, Pos(Top, 1), Pos(Top, 1), Select(1, 0))),

Remove(Row, RowCnd(Interval, Pos(Top, 13), Pos(Top, 21), Select(1, 0)))

));

Re: Creating Calculated dimension of a range

sinanozdemir — Sat, 22 Aug 2015 22:33:57 GMT

Here is how I did it in the front-end by using Class() function:

I am also attaching the qvw.

Hope this helps.

Thanks

Re: Creating Calculated dimension of a range

petter — Sat, 22 Aug 2015 22:34:06 GMT

Or if you prefer do have the "raw data" in the in-memory database you could create a straight table and put in:

Region and Client as dimensions and Sum(Amount) (and name/label it Amt) as first expression and this expression:

If( Amt < 5000 , '<5000' ,

If( Amt < 15000 , '5000-15000' ,

If( Amt < 30000 , '15000-30000' ,

'>=30000' ))

as second expression.

Re: Creating Calculated dimension of a range

zagzebski — Mon, 24 Aug 2015 02:54:59 GMT

Took your expression made a few modifications and it worked great. Thanks!

Re: Creating Calculated dimension of a range

sinanozdemir — Mon, 24 Aug 2015 12:44:53 GMT

You are welcome.

I am glad it was helpful.