- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Creating a dimension based on Measure
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Sandra,
Can you give an example of your data structure?
Mike
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Thank you. It worked.
Sandra
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Also have a look at the Class Function:
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 .