Calculated Dimension

[#1]

Hello ,

I am attempting to use a calculated dimension in a table. I would like this dimension to look at units sold (sum(unitssold)) and create categories by tens. So the first category in the would count the amount of "customers" who have sold 0-10 items. The next 11-20 and so on. My expression is count(Distinct CustomerID). How would I create this calculated demension?

Thanks!

[#2]

Although it's a bit tedious, the easiest way I can think of (assuming you don't have too many sets of 10) is a nested IF statement:

If(count(Distinct CustomerID)<11, '0-10', If(count(Distinct CustomerID)<21,'11-20',IF.....)))

and so on

[#3]

Hi Jhoffman,

using if is an option but a bit tedious. So instead just create an inline table showing the intervals. Get the count(distict CustomerID) as count .then create the interval table using another inline and do a comparison using the interval atch function. The interval table looks like this.

a:

Load

count(distinct CustomerID) as count

from

interval:

CountStart Count End Interval

0 10 0-10

11 20 11-20

21 30 21-30

thenuse the interval matching function as given below

intervalmatch(Count) Load CountStart,CountEnd resident interval;

hope this helps you,

Thanks Joseph.......

[johnw]

Real dimensions are faster than calculated dimensions. So solving it during the load with nested IFs or a classification table and intervalmatch will give you better chart performance. But sometimes it's nice to have options, so here's a simple calculated dimension option that I think would work:

class(count(distinct CustomerID),10)

[#5 · Accepted solution]

Actually, i think you need to aggregate the items and then class them

Dimension:

CLASS(AGGR(SUM(NoOfSoldItems), CustomerID), 10)

Expression:

COUNT(DISTINCT CustomerID)

[johnw]

Whoops, yeah, you're right. My expression is useless without aggregating across customers, and CustomerID isn't what we're supposed to be counting in the aggr, only in the expression. Double oops.