Topic
  • 5 replies
  • Latest Post - ‏2012-08-01T17:52:49Z by Rizwan.Kaif
SteveW.
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
    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.
    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
    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.
    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
    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.