topic Re: Help on calculated dimension in QlikView

Help on calculated dimension

Sun, 12 Aug 2012 11:05:41 GMT

Hi gurus,

Can somebody help me in this. I have for example data in the following format

CustNo	Itemcode	Value
1	abc	200
1	pqr	300
1	xyz	40
2	pqr	50
3	abc	100
3	xyz	250

client 1 sum is 540

client 2 sum is 50

client 3 sum is 350

I would like to display the output as

category	Header 2
client with total value > 300	2
client with total value between 300 and 200	0
client with total value < 200	1

Re: Help on calculated dimension

swuehl — Sun, 12 Aug 2012 11:21:48 GMT

What you need here is advanced aggregation in your calculated dimension (please check the aggr() function in the Help and its linked examples):

As dimension

=aggr( sum(Value), CustNo)

and as expression

=count(distinct CustNo)

This will draw the sum(Value) as dimension and then the count of distinct CustNo as expression value, still keeping the link from CustNo to their sum(Value).

You then need to group / classify your sum(Value) into your buckets, so change your calculated dimension into:

=aggr(

if(sum(Value) < 200, 'client with total value <200',

if( sum(Value) <=300, 'client with total value between 300 and 200','client with total value > 300'))

, CustNo

)

leaving the expression as is.

Hope this helps,

Stefan

Re: Help on calculated dimension

Mon, 13 Aug 2012 07:00:56 GMT

You can also make use of "CLASS"

class( expression, interval [ , label [ , offset ]] )
Creates a classification of expressions. The bin width is determined
by the number set as interval. The result is shown as a<=x<b, where
a and b are the upper and lower limits of the bin. The x can be
replaced by an arbitrary string stated in label. 0 is normally the
default starting point of the classification. This can be changed by
adding an offset.
Examples:
class( var,10 ) with var = 23 returns '20<=x<30'
class( var,5,'value' ) with var = 23 returns '20<= value <25'
class( var,10,'x',5 ) with var = 23 returns '15<=x<25'