Sounds like: - your minimum granularity for queries are on Weeks, your fact table need be on weeks (or less, like days) - you will need expand you actual fact table to weeks (or more, days) Example use a hive view - as extra: Kylin can't use partition format columns on weeks, the minimum es days
Alb On 28 February 2018 at 21:51, deva namaste <ohd...@gmail.com> wrote: > Hello, > > How would I calculate value for a week while I have bi-monthly values. > > e.g. Here is my data looks like - > > Date - Value > 01/18/2017 - 100 > 03/27/2017 - 130 (68 Days) > 05/17/2017 - 102 (51 Days) > > I need average value per week, as below. Lets consider between 03/27 and > 05/17. So total days between period are 51. so Daily average would be > 102/51= 2.04 > > Week4 (Starting March 26, #days = 4) = (4 x 2.04) = 8.16 > Week1 (Starting Apr 2, #days = 7) = 14.28 > Week2 (starting Apr 9, #days = 7)= 14.28 > Week3 (starting Apr 16, #days = 7)= 14.28 > Week4 (starting Apr 23, #days = 7)= 14.28 > week5 (Starting Apr 30, #days =7)= 14.28 > week1 (starting May 7, #days = 7)= 14.28 > Week2 (starting May 14, #days = 4)= 8.16 > > But as you see that period from 01/18 to 03/27, have 68 days and daily > average would be 130/68=1.91 > > So really to get complete week I need 3 days from 130 value and 4 days > from 102 value. > > So real total for that first week would be - > Week4 (Starting March 26, #days = 4) = (4x2.04=8.16) + (3x1.91=5.73) = > 13.89 > > How would I achieve this in Kylin? Any function? or other method I can > use? > Just for 6 records for year, I dont want to populate daily records. > Thanks > Deva > > >
