topic Calculated Dimension in QlikView

Calculated Dimension

Mon, 10 May 2010 15:17:20 GMT

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!

Calculated Dimension

Mon, 10 May 2010 15:21:03 GMT

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

Calculated Dimension

Mon, 10 May 2010 15:35:15 GMT

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.

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.......

Calculated Dimension

johnw — Mon, 10 May 2010 20:35:40 GMT

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)

Calculated Dimension

Mon, 10 May 2010 21:36:54 GMT

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

Dimension:

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

Expression:

COUNT(DISTINCT CustomerID)

Calculated Dimension

johnw — Mon, 10 May 2010 21:44:21 GMT

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.