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)
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?
NOTICE: 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.
This topic has been locked.
2 replies Latest Post - 2012-04-23T13:32:14Z by Xwh_CA
Pinned topic Avg Quantity per Number of Days in a Month
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-04-23T13:32:14Z at 2012-04-23T13:32:14Z by Xwh_CA
DanielWagemann 2700058MAJ850 PostsACCEPTED ANSWER
Re: Avg Quantity per Number of Days in a Month2012-04-20T17:53:57Z in response to IMTKSLIf 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 2700059Q801 PostACCEPTED ANSWER
Re: Avg Quantity per Number of Days in a Month2012-04-23T13:32:14Z in response to IMTKSL1. 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)