6 Replies Latest reply: Jan 4, 2011 7:42 AM by martinohlson

# Calculated dimension to only show a subset of a table

Hi,

I want to add a table as a dimension for a graph, but I don't want the full table to be included in the graph, only a part of it.

I think this is a problem solved with calculated dimensions (correct me if I'm wrong), but I don't know the syntax for it.

I have a table Customers with customer ID 1, 2, 3, 4, 5. Instead of using the dimension "customerID" (which would result in 1-5) I only want to include the customer with the highest customer id. What is the syntax for this calculation? I'm guessing something like "=max(customerID)"?

If I only want to add the customers who have made over 10 orders, count(orderid)>=10, how do I add this restriction to my dimension?

• ###### Calculated dimension to only show a subset of a table

hei attach is an example

hope its helps you

you can play with the variable give you more then one customer

• ###### Calculated dimension to only show a subset of a table

Thanks again RoiUser!

Is there a way to write the code so I don't need to use the variable maxCust?

Like:

aggr(if(Custid>=Max(Custid),Custid),Custid)

aggr(if(Custid>=maxCust,Custid),Custid)

I tried just replacing the varible call "maxCust" with the acctual content of the variable "Max(Custid)" but it didn't work.

• ###### Calculated dimension to only show a subset of a table

Also, is there a way to use Rank? So I can compare the values by relative size instead of acctual value? I figured Rank is better if the values aren't in perfect order (CustID 4, 6, 7, 9, 10, 13).

• ###### Calculated dimension to only show a subset of a table

OK, i figured both things out for my self =)

The code I should use is:

=aggr(if(rank(Custid)<=3,Custid),Custid)

Thank you RoiUser!

• ###### Calculated dimension to only show a subset of a table

When I use my aggr rank function mentioned above on another dataset i get the result I want with the top 3, but it also includes an extra tuple which is a sum of the non-included tuples.The ghost tuple doesn't have a name, only a "-"

Example:

Adam 5, John 10, Dennis 8, Anna 12, Mat 6.

Top 3 would be Anna, John, Dennis. But as a result I get those three and then a fourth called "-" which has the value 11 (which is adam 5 + mat 6).

How do I get rid of the ghost value??

________

Edit:

FOUND IT! There's a tick box saying "don't show null values". Tada!

• ###### Calculated dimension to only show a subset of a table

At the Back end, you can create a flag with this condition and on the front end you can use If(condition = True,CustomerId) in the calculated dimension.

Hope it works ....