Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
2 replies Latest Post - ‏2012-04-23T13:32:14Z by Xwh_CA
IMTKSL
IMTKSL
1 Post
ACCEPTED ANSWER

Pinned topic Avg Quantity per Number of Days in a Month

‏2012-04-20T00:58:30Z |
Dear Experts,

Given a simple data model like the below:

Sales Order Fact (Transaction Date SK, Product SK, Quantity)
Product Dimension (Product SK, Product Code, Product Desc)
Date Dimension (Date SK, Date, Year, Year Month, Quarter)

And data:
Transaction Date, Product, Quantity Ordered
2011-01-01, 'Green Tea', 10
2011-01-05, 'Red Tea', 20
2011-02-04, 'Green Tea', 20
2011-02-07, 'Red Tea', 10
2011-04-15, 'Green Tea', 10
2011-04-20, 'Green Tea', 20
2011-04-25, 'Red Tea', 30
2011-08-01, 'Green Tea', 20
2011-12-31, 'Red Tea', 10

With Business Insight Advanced (BIA), when I am going to calculate the average of quantity ordered, it is natural to give the following results by the tool:
2011-01: (10+20) / (1+1)
2011-02: (20+10) / (1+1)
2011-04: (10+20+30) / (1+1+1)
2011-08: (20) / (1)
2011-12: (10) / (1)

However the user requirement is to have the quantity be divided by the number of days in that month, that is as follows:
2011-01: (10+20) / (31)
2011-02: (20+10) / (28)
2011-04: (10+20+30) / (30)
2011-08: (20) / (31)
2011-12: (10) / (31)
And if year then
2011: (10+20+20+10+10+20+30+20+10) / (365)

So might I know how this could be achieved in the BIA?
Updated on 2012-04-23T13:32:14Z at 2012-04-23T13:32:14Z by Xwh_CA
  • DanielWagemann
    DanielWagemann
    850 Posts
    ACCEPTED ANSWER

    Re: Avg Quantity per Number of Days in a Month

    ‏2012-04-20T17:53:57Z  in response to IMTKSL
    If your Time dimension has a days in month field, you could create the average and total year average manually as its own column.

    So you would have:
    Year(Grouped) Year-Month(Grouped) DaysInMonth Product Line Quantity Year-MonthAvgCalc and YearAvgCalc

    Year-MonthAvgCalc would be (total(Quantity for Year,Year-Month)/DaysInMonth)

    YearAvgCacl would be total(Quantity for Year)/ total (DaysInMonth for Year)
  • Xwh_CA
    Xwh_CA
    1 Post
    ACCEPTED ANSWER

    Re: Avg Quantity per Number of Days in a Month

    ‏2012-04-23T13:32:14Z  in response to IMTKSL
    1. Assuming your time table is fully defined from Jan 01 to Dec 31.
    2. change the relationship between time and salse from 1:1--1:n to 1:1 --0:n
    3. define your avg as total(QTY) / count(rows)