In this query (query table) along with complete year 2012
sales data, two months of 2013 sales data also exists. The
query has been modified using the
quarter query
syntax to split the months into quarters based on the month
as well as the year. The NTILE() cannot be used since it will
split the 2013 data into quarter 1 and 2 instead of 1 both the
months. Due to existence of two year data (2012 and 2013), the
Ratio_To_Report function has to partition
data by year for monthly ratio analysis (MONTHLY_%) -
RATIO_TO_REPORT(SUM(sales))
OVER(PARTITION BY SUBSTR(q_dw.year_id,1,4))
The sum of percentages for 2012 will add to 100% and 2013 also
will add to 100% but just for two months. In
the quarterly ratio computation, the
Ratio_To_Report function has to partition
data by year and quarter for quarterly ratio analysis
(QUARTERLY_%) -
For 2012, the Ratio_To_Report computes the value of
Jan/(Jan+Feb+Mar) + Feb/(Jan+Feb+Mar) + Mar/(Jan+Feb+Mar)
which will equal 100% (as shown below) ->
(11000/35000 + 12000/35000 + 14000/35000) = (0.297+0.324+0.378) = 1
For 2013, since there are just two month data, the
the Ratio_To_Report computes the value of
Jan/(Jan+Feb) + Feb/(Jan+Feb) = 1
(14200/27450) + (13250/27450) = (0.517 + 0.483) = 1