Creating Calculated dimension of a range

[zagzebski]

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

[petter]

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

));

[sinanozdemir · Accepted solution]

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

I am also attaching the qvw.

Hope this helps.

Thanks

[petter]

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.

[zagzebski]

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

[sinanozdemir]

You are welcome.

I am glad it was helpful.