Topic
• 5 replies
• Latest Post - ‏2012-08-01T17:52:49Z by Rizwan.Kaif
SteveW.
13 Posts

# Pinned topic Doing "Counts" in TM1

‏2012-07-30T16:46:19Z |
Customer has repair work orders. Each repair order has a unique business Key in the DW.
On each repair order is a "line item".

Customer wants to be able to average dollars per repair order.
and
Avg line items per repair order.

what is the best way to store counts like this in TM1 ?
Updated on 2012-08-01T17:52:49Z at 2012-08-01T17:52:49Z by Rizwan.Kaif
• Rizwan.Kaif
197 Posts

#### Re: Doing "Counts" in TM1

‏2012-07-30T17:34:24Z
There are a couple of ways of dealing with this. You can create a cube which consists of Order Dimension and Line number dimension. You will have to create a Count Measure (numeric). In this measure you can load 1 with every transaction (order & line number) combination. The Count measure will help you to count the number of Lines per order. Later you can create a Average measure which will have C level Rule to calculate the averages.

Alternatively you can also make use of the ConsolidatedAvg() function in TM1.
Cheers!
Rizwan Kaif
• SteveW.
13 Posts

#### Re: Doing "Counts" in TM1

‏2012-07-30T19:43:46Z
There are a couple of ways of dealing with this. You can create a cube which consists of Order Dimension and Line number dimension. You will have to create a Count Measure (numeric). In this measure you can load 1 with every transaction (order & line number) combination. The Count measure will help you to count the number of Lines per order. Later you can create a Average measure which will have C level Rule to calculate the averages.

Alternatively you can also make use of the ConsolidatedAvg() function in TM1.
Cheers!
Rizwan Kaif
So I can have more than one measure dimension?

I still want to measure dimension for total sales, quantity, and such.
Now I can add another "count dimension" and put in the same cube with the other sales measures?
Which would allow me to do sales per Order and sales per line?
• Rizwan.Kaif
197 Posts

#### Re: Doing "Counts" in TM1

‏2012-07-30T20:27:01Z
• SteveW.
• ‏2012-07-30T19:43:46Z
So I can have more than one measure dimension?

I still want to measure dimension for total sales, quantity, and such.
Now I can add another "count dimension" and put in the same cube with the other sales measures?
Which would allow me to do sales per Order and sales per line?
No you dont need to have a separate Measure dimension. You just have to create a Count measure in the current measure dimension.

But you do need to have a Line_Number dimension - which will have line numbers say from 1 to 1000 (or whatever number your source system has got)

Cheers!
Rizwan Kaif
• SteveW.
13 Posts

#### Re: Doing "Counts" in TM1

‏2012-07-31T14:52:21Z
No you dont need to have a separate Measure dimension. You just have to create a Count measure in the current measure dimension.

But you do need to have a Line_Number dimension - which will have line numbers say from 1 to 1000 (or whatever number your source system has got)

Cheers!
Rizwan Kaif
So, I create a dimension "Repair Order" (that would probably never be used in a report), whose members were "Repair Order" whose Business Key was the member and "Repair Line" whose Business Key was it's member.

Then create a two measures:
Order count: Count 1 within set Repair Order
Line item count: Count 1 within set Repair Line

Is this how to implement it?
• Rizwan.Kaif
197 Posts

#### Re: Doing "Counts" in TM1

‏2012-08-01T17:52:49Z
• SteveW.
• ‏2012-07-31T14:52:21Z
So, I create a dimension "Repair Order" (that would probably never be used in a report), whose members were "Repair Order" whose Business Key was the member and "Repair Line" whose Business Key was it's member.

Then create a two measures:
Order count: Count 1 within set Repair Order
Line item count: Count 1 within set Repair Line

Is this how to implement it?
Your cube should look like somewhat as shown in the attachment.

Count, Amount and Average are Measures in the attached screenshot.