Calculated Dimension using Count Distinct by Vendor

[#1]

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

[Anonymous · Accepted solution]

Try to replace each

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

with this

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

[ramoncova06]

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'

[Anonymous]

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.

[ramoncova06]

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

[#6]

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!

[Anonymous]

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

[#8]

I like nested IF .

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

[Anonymous]

Not sure about faster, but at least looks cleaner.