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.
Avg line items per repair order.
what is the best way to store counts like this in TM1 ?
This topic has been locked.
5 replies Latest Post - 2012-08-01T17:52:49Z by Rizwan.Kaif
Pinned topic Doing "Counts" in TM1
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-08-01T17:52:49Z at 2012-08-01T17:52:49Z by Rizwan.Kaif
Re: Doing "Counts" in TM12012-07-30T17:34:24Z in response to SteveW.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.
Re: Doing "Counts" in TM12012-07-30T19:43:46Z in response to Rizwan.KaifSo 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?
Re: Doing "Counts" in TM12012-07-30T20:27:01Z in response to SteveW.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)
Re: Doing "Counts" in TM12012-07-31T14:52:21Z in response to Rizwan.KaifSo, 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?