topic Re: Calculated Dimension using Count Distinct by Vendor in QlikView

Calculated Dimension using Count Distinct by Vendor

Wed, 06 May 2015 17:50:17 GMT

Hello,

I'm trying to define a dimension that will group the # of POs per vendor as shown below.

<10 10-100 100-200 200-500 >500

I have the following that works correctly as an expression to get the # per vendor:

=Count((($<[Vendor ID]>} distinct PO)

I added that to IF statements in the calculated dimension as follows, but keep getting an error message returned.

If I put the above into a text box, >500 is displayed.

Anyone know what I'm missing in the dimension?

Thank you in advance.

-P

Re: Calculated Dimension using Count Distinct by Vendor

Anonymous — Wed, 06 May 2015 19:01:48 GMT

Try to replace each

count({<[Vendor ID]>} distinct PO)

with this

aggr(count(distinct PO),[Vendor ID])

Re: Calculated Dimension using Count Distinct by Vendor

ramoncova06 — Wed, 06 May 2015 19:03:19 GMT

I recommend doing this at script level, or you might be able to use class function

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'

Re: Calculated Dimension using Count Distinct by Vendor

Anonymous — Wed, 06 May 2015 19:13:08 GMT

My first thought was the class() function, but the nature of intervals in this case doesn't help.

Script level solution is a better idea. If the buckets are permanent, selections-independent, it will work fine and improve performance. But if the result must depend on selections, then it can't be done in the script.

Re: Calculated Dimension using Count Distinct by Vendor

ramoncova06 — Wed, 06 May 2015 19:19:58 GMT

Agreed, I really just wanted to give some options besides having some nested IF's

Re: Calculated Dimension using Count Distinct by Vendor

Wed, 06 May 2015 19:25:13 GMT

Thank you very much for the quick responses! I was able to resolved the issue using the Aggr function on the PO as suggested by Michael.

I couldn't do it via script as the values will change depending on the user filter selections.

Thanks again!

Re: Calculated Dimension using Count Distinct by Vendor

Anonymous — Wed, 06 May 2015 19:33:04 GMT

Nobody likes nested IF

I think that it should work even if have only one "external" aggr instead of many "internal":

aggr(

if(count(distinct PO)<10,'<10',

....

)

,[Vendor ID])

Re: Calculated Dimension using Count Distinct by Vendor

Wed, 06 May 2015 19:48:12 GMT

I like nested IF .

Using Aggr "external" worked as well. Does this method process faster than "internal", or just for ease of understanding/structure?

Re: Calculated Dimension using Count Distinct by Vendor

Anonymous — Wed, 06 May 2015 19:51:38 GMT

Not sure about faster, but at least looks cleaner.