Creating a dimension based on Measure

[Anonymous]

Hi,

I have created a master Item - Measure - that is meant to count the number of orders on my Data Set. This count is based on the OrderNumber field. My data set has more than one row for each Order. Therefore my Measure looks like this:

Count(distinct OrderNumber)

Now, I want to create a new dimention based on this measure. The dimention needs to count the number of orders so that it divides the order count into 3 groups:

Group 1: Orders that contain less than 30 product items.

Group2: Orders with 31-200 product items.

Group 3: Orders with more than 200 product items.

The product item field is names "Qty"

My end goal is to show how many orders I have with less than 30 items, how many orders with 31-200 items and how many orders with more than 200 items.

Hope I was clear

Mant thanks in advance

Sandra

[mgranillo]

Sandra,

Can you give an example of your data structure?

Mike

[Anonymous]

Hi,

Sure. Here it is:

OrderNumber	QTY
SO11000688	6
SO11000689	10
SO11000689	18
SO11000687	1
SO11000720	8
SO11000720	2
SO11000720	2
SO12000009	5
SO12000009	13
SO12000009	15
SO12000009	18
SO11000734	20
SO11000734	24

Thanks

Sandra

[mgranillo]

try

if(aggr(sum(QTY),OrderNumber)<31,'group1'

,if(aggr(sum(QTY),OrderNumber)<201,'group2','group3'))

for you dimension and count(distinct OrderNumber) for your measure

[Anonymous]

Thank you. It worked.

Sandra

[#6]

Also have a look at the Class Function:

[oknotsen]

If your question is now answered, please flag the Correct Answer (via the big "Correct Answer" button near every post) and Helpful Answers (found under the Actions menu under every post).

If not, please make clear what part of this topic you still need help with .