convert 13 weeks of data to calendar months

[hag]

I have a 13 column browse. Each column represents a week. The weeks are consecutive.
The Browse looks like this:

Headers: 7/15/10-07/22/10-07/29/10-08/05/10-8/12/10-08/19/10-08/26/10-09/02/10-09/09/10-09/16/10-09/23/10-09/30/10

As you can see the 13 week columns span three calendar months. July, August and September. Each of the columns contains a value for the week indicated.

My need is to convert the 13 week information to calendar dates by the number of days in a month. As you know there are 13 week’s in the calendar quarter. July has 31 days, August has 31 days in September has 30 days.

Has anyone put together some code converting the 13 weeks of data into three calendar months. The biggest problem comes about when a week spans two months. Thus some of the values in that week need to go to the month before and some need to go to the month after.

For example the August 5-week needs to be split between July and August by days. And the September 2 week needs to be split between August and September by days.
The per day value is developed by taking the weeks value and dividing by 7.

All help will be appreciated.

[James Bott]

Harvey,

I read you message a couple of times and I am still not clear on what you are trying to do. Are you saying that you already have data for each week, and now you want to convert it to data for each month?

If so, I would find the data for each day by dividing the week data by 7, then summarize the data by adding all the daily data for each month.

Create a two-dimesional array with two elements; one for the date, and one for the total. Then fill the array with dates and totals, then summarize for each month.

James

[hag]

Thanks for the response.

Problem is when a week covers 2 months. Need to have an easy method to slpit the week up between the months
The main issue is to know how many days in the month so you only account for the days in the month, no more no less. say july 31days ...week one 3 days in June and 4 days in july, week two has 7 days in july, week 3 has 7 days july, week 4 has 7 days july, week 5 has 6 days in july and one day in August.
July week1 = 4 days
week2 = 7 days
week3 = 7 days
week4 = 7 days
week5 = 6 days
total days 31
Just looking for an easy way to accomplish this without many lines of code.
I can do it but take lot of time. Maybe with you skills you have a suggestion. If you call I'm not a programer. Just an accountant with one program.

Thanks for any suggestions.

[ukoenig]

Hello Harvey,

maybe that could work for You ???

Your Question :
Problem is when a week covers 2 months.
Need to have an easy method to slpit the week up between the months

The Calculation is included in my Date / Time Funktion-Collection





Of any given date :
1. calculates the weeknumber
2, 3. calculates the start- and enddate of the weeknumer.

it shows, if the calculated week of a given date is included in one Month or two.

Best Regards
Uwe

[James Bott]

You guys are over complicating it.

By calculating the total for each day you can easily figure out how much for each month--you don't need to know when a week is split between two months. Forget weeks after converting the data to days.

You can use the Month( dDate ) function to find all days in a month--you don't need to figure out how many days in each month; the month() function will take care of it.

You fill an array with the data (date and amount) then start processing the array until you come to a new month, then start over with a new total.

James

[hag]

Uwe:
it is a bit complex. I want the user to enter on one button and it converts 13 weeks to 3 calendar months.

James:
Each of the 13 weeks has a different value. So the allocation of days within a week to the month must be made.
This has to be done the first week and last week of the month.

so far what I'm doing is taking 5 weeks to determine the months value.
week1 can be between split between 2 months
week2, week3, week4 always seven days and always same month
week5 makes up the difference in days between the total days used in week 1 to 4 compared to number of days in the month.

[James Bott]

Harvey,

Each of the 13 weeks has a different value. So the allocation of days within a week to the month must be made. This has to be done the first week and last week of the month.

Understood. But as I said, the month() function will handle this for you.

Here is a sample done with the month() function. First the data is converted to daily data, then the daily data is summed by month. Simple.

Start with weekly data:

Week Starting Amount
1 07/15/10 100
2 07/22/10 122
3 07/29/10 127
4 08/05/10 117

Convert to daily data:

Date Amount
07/15/10 14.29 (100/7)
07/16/10 14.29
07/17/10 14.29
07/18/10 14.29
07/19/10 14.29
07/20/10 14.29
07/21/10 14.29
07/22/10 17.43 (122/7)
07/23/10 17.43
07/24/10 17.43
07/25/10 17.43
07/26/10 17.43
07/27/10 17.43
07/28/10 17.43
07/29/10 18.14 (127/7)
07/30/10 18.14
07/31/10 18.14

Find monthly data:
Month total 276.43

Isn't this what you are looking for?

James

[nageswaragunupudi]

Further simplifying the above logic calculate aggregate for the quarter and adjust for first and last weeks.
When the first or last week extends to previous or next quarter, reduce proportionately by that week's daily average.

It also happens that first week starts later than the 1st day of calendar quarter or last day ends earlier than the last of day of calendar quarter. In such cases we need values for last week of previous 52wk quarter or 1st week of next quarter to get accurate result. If we don't have those values, we need to extrapolate this quarter's tail week values.

For more accurate conversions we also need to know if the transactions are 5days or 6days or 7days a week.

A generic function can be attempted which requires the following paramters:
1. Aggregate of 13week quater.
2. First date of the quarter
3. First week value
4. Last week value
Optionally
5. Last week value of previous quarter
6. First week value of next quarter

Here is a quick attempt ( untested )

Code: Select all  Expand view

function Q13ToCalQuarter( nTotal, dFirstDay, nFirstWeek, nLastWeek, nPrevWeek, nNextWeek )

   local n

   n  := DAY( dFirstDay ) - 1
   if n > 0
      if n < 7
         nTotal   += ( n * If( nPrevWeek == nil, nFirstWeek, nPrevWeek ) / 7 )
      else
         n  := EOM( dFirstDay ) - dFirstDay + 1
         nTotal  -= ( n * nFirstWeek / 7 )
      endif
   endif
   dFirstDay   += 91    // first day of next quarter
   n  := DAY( dFirstDay ) - 1
   if n > 0
      ? n
      if n < 7
         nTotal   -= ( n * nLastWeek / 7 )
      else
         n  := EOM( dFirstDay ) - dFirstDay + 1
         nTotal   += ( n * If( nNextWeek == nil, nLastWeek, nNextWeek ) / 7 )
      endif
   endif

return nTotal


 

[James Bott]

Rao,

You bring up a significant point--what are the rules?

I note that in Harvey's original message he shows only 12 weeks, yet he specifies 13 weeks. And the sample data he shows starts on 7/15/10. Thirteen weeks from 7/15/10 ends on 10/13/10.

I would assume that monthly data for three months should be the range from 7/1/10 to 9/30/10. If that is the range needed, then it is not possible to convert the 13 weeks of data specified to monthly data. You would actually need data from the previous couple of weeks.

It would be ideal to have the daily data for each day of the year (YTD) and from that you can find weekly and monthly data with accuracy.

It has been some time since I studied accounting so I don't know what the conventional methods are.

Regards,
James

[hag]

so far I have a solution except the 13 week period must start at the beginning of a month or within 7 days before the beginning of the month.

heres my code:

Code: Select all  Expand view

function airrpt()
   local cDate99 //:= date()-17
   local cDate1  
   local cDate2  
   local cDate3  
   local cDate4  
   local cDate5  
   local cDate6  
   local cDate7  
   local cDate8  
   local cDate9  
   local cDate10
   local cDate11
   local cDate12
   local cDate13, nVar := 0
   local nTotal1 :=0
   local nTotal2 :=0
   local nTotal3 :=0
// weekly values 
 local nCf1  :=  700 
   local nCf2  := 1400 
   local nCf3  := 2100 
   local nCf4  := 2800 
   local nCf5  := 3500

   local nCf6  := 4200
   local nCf7  := 4900 
   local nCf8  := 5600 
   local nCf9  := 6300 
   local nCf10 := 7000 

   local nCf11 := 7700 
   local nCf12 := 8400 
   local nCf13 := 9100 

   local nCf1a  := 0   
   local nCf5a  := 0

   local nNum1  := 0   
   local nNum2  := 0   
   local nNum3  := 0   
   local nNum4  := 0   
   local nNum5  := 0   
   local nNum6  := 0   
   local nNum7  := 0   
   local nNum8  := 0   
   local nNum9  := 0   
   local nNum10 := 0   
   local nNum11 := 0   
   local nNum12 := 0   
   local nNum13 := 0   

   local nDate1, nDays := 0
   local nDate2  
   local nDate3  
   local nDate4  
   local nDate5  
   local nDate6  
   local nDate7  
   local nDate8  
   local nDate9  
   local nDate10
   local nDate11
   local nDate12
   local nDate13

   use "20100701.d10"   alias Mthly
   index on mthly->account to nMthly

   cDate99 := ctod("07/01/10")     
   cDate1  := cDate99+6
   cDate2  := cDate99+(7*2) -1
   cDate3  := cDate99+(7*3) -1
   cDate4  := cDate99+(7*4) -1
   cDate5  := cDate99+(7*5) -1
   cDate6  := cDate99+(7*6) -1
   cDate7  := cDate99+(7*7) -1
   cDate8  := cDate99+(7*8) -1
   cDate9  := cDate99+(7*9) -1
   cDate10 := cDate99+(7*10)-1
   cDate11 := cDate99+(7*11)-1
   cDate12 := cDate99+(7*12)-1
   cDate13 := cDate99+(7*13)-1

   cDate1  := dtoc(cDate1)  
   cDate2  := dtoc(cDate2)     
   cDate3  := dtoc(cDate3)  
   cDate4  := dtoc(cDate4)  
   cDate5  := dtoc(cDate5)  
   cDate6  := dtoc(cDate6)  
   cDate7  := dtoc(cDate7)  
   cDate8  := dtoc(cDate8)  
   cDate9  := dtoc(cDate9)  
   cDate10 := dtoc(cDate10)
   cDate11 := dtoc(cDate11)
   cDate12 := dtoc(cDate12)
   cDate13 := dtoc(cDate13)

   mthly->(dbgotop())
   do while mthly->account < "9850" 

// getting weekly values from 13 week report
      nCf1  := mthly->cf1
      nCf2  := mthly->cf2
      nCf3  := mthly->cf3
      nCf4  := mthly->cf4
      nCf5  := mthly->cf5
      nCf6  := mthly->cf6
      nCf7  := mthly->cf7
      nCf8  := mthly->cf8
      nCf9  := mthly->cf9                          
      nCf10 := mthly->cf10
      nCf11 := mthly->cf11
      nCf12 := mthly->cf12                 
      nCf13 := mthly->cf13

      if nCf1 +;
         nCf2 +;
         nCf3 +;
         nCf4 +;
         nCf5 +;
         nCf6 +;
         nCf7 +;               
         nCf8 +;
         nCf9 +;
         nCf10+;
         nCf11+;
         nCf12+;
         nCf13  <= 0

         mthly->(dbskip()) 
         loop
      endif

      //Month 1 

      nNum1 := 7-(val(SUBSTR(cDate1,4,2)))  
      nNum2 := 0  // always 7 days
      nNum3 := 0  // always 7 days
      nNum4 := 0  // always 7 days
      nNum5 := 7-(val(SUBSTR(cDate5,4,2)))       

      // ADD TO THE BEGINNING OF THE MONTH
      nCf1a := nCf1/7 // 1st    week daily average
      nCf5a := nCf5/7 // last   week   "

      if nNum1 == 0
         nNum1 := nCf1
      else      
         nNum1 := 7-nNum1
         nNum1 := nNum1*nCf1a
      endif        
      if nNum2 == 0
         nNum2 := nCf2
      endif        
      if nNum3 == 0
         nNum3 := nCf3
      endif        
      if nNum4 == 0
         nNum4 := nCf4
      endif        
      if nNum5 == 0
         nNum5 := nCf5
      else      
         nNum5 := nNum5*nCf5a
      endif        

      nTotal1 := nNum1+;               
      nNum2+;              
      nNum3+;              
      nNum4+;              
      nNum5            

      //Month 2 

      nNum5 :=  7-nNum5/nCf5a   
      nNum6 := 0
      nNum7 := 0
      nNum8 := 0
      nNum9 := 7-(val(SUBSTR(cDate9,4,2)))       

      nCf1a := nCf5/7 // 1st    week
      nCf5a := nCf9/7 // last   week

      if nNum5 == 0
         nNum5 := nCf5
      else      
         nNum5 := nNum5*nCf1a
      endif        
      if nNum6 == 0
         nNum6 := nCf6
      endif        
      if nNum7 == 0
         nNum7 := nCf7
      endif        
      if nNum8 == 0
         nNum8 := nCf8
      endif        
      if nNum9 == 0
         nNum9 := nCf9
      else      
         nNum9 := nNum9*nCf5a
      endif        

      nTotal2 := nNum5+;               
      nNum6+;              
      nNum7+;              
      nNum8+;              
      nNum9            

      //Month 3 

      nNum9  := 7-nNum9/nCf5a   
      nNum10 := 0
      nNum11 := 0
      nNum12 := 0
      nNum13 := 7-(val(SUBSTR(cDate13,4,2)))         

      if nNum13 < 0
         nNum13 := 0
      endif

      nCf1a := nCf9/7 // 1st    week
      nCf5a := nCf13/7 // last  week

      if nNum9 == 0
         nNum9 := nCf9
      else      
         nNum9 := nNum9*nCf1a
      endif        
      if nNum10 == 0
         nNum10 := nCf10
      endif        
      if nNum11 == 0
         nNum11 := nCf11
      endif        
      if nNum12 == 0
         nNum12 := nCf12
      endif        
      if nNum13 == 0
         nNum13 := nCf13
      else      
         nNum13 := nNum13*nCf5a
      endif        

      nTotal3 := nNum9+;               
      nNum10+;             
      nNum11+;             
      nNum12+;             
      nNum13               

      // which quarter here

      mthly->mth7 := nToTal1
      mthly->mth8 := nToTal2
      mthly->mth9 := nToTal3


      mthly->mth13 := mthly->mth1 +;
      mthly->mth2 +;
      mthly->mth3 +;
      mthly->mth4 +;
      mthly->mth5 +;
      mthly->mth6 +;
      mthly->mth7 +;
      mthly->mth8 +;
      mthly->mth9 +;
      mthly->mth10 +;
      mthly->mth11 +;
      mthly->mth12

      * ? nToTal1   +;
      * nToTal2 +;
      * nToTal3                        

      nNum1  := 0   
      nNum2  := 0   
      nNum3  := 0   
      nNum4  := 0   
      nNum5  := 0   
      nNum6  := 0   
      nNum7  := 0   
      nNum8  := 0   
      nNum9  := 0   
      nNum10 := 0                                      
      nNum11 := 0   
      nNum12 := 0   
      nNum13 := 0          
      mthly->(dbskip())
      loop
   enddo   
     set filter to !mthly->mth13 == 0   
       
     //browse()
   //newBrowse()
   return nil
 

I've tested if for 13 weeks beginning 6/01/0...7/07/10...5/01/10
It works.

[James Bott]

Harvey,

I think you would benefit by learning to use arrays. This can signigicantly reduce your coding time.

Example. This code can be reduced:

Code: Select all  Expand view

  cDate99 := ctod("07/01/10")      
   cDate1  := cDate99+6
   cDate2  := cDate99+(7*2) -1
   cDate3  := cDate99+(7*3) -1
   cDate4  := cDate99+(7*4) -1
   cDate5  := cDate99+(7*5) -1
   cDate6  := cDate99+(7*6) -1
   cDate7  := cDate99+(7*7) -1
   cDate8  := cDate99+(7*8) -1
   cDate9  := cDate99+(7*9) -1
   cDate10 := cDate99+(7*10)-1
   cDate11 := cDate99+(7*11)-1
   cDate12 := cDate99+(7*12)-1
   cDate13 := cDate99+(7*13)-1

   cDate1  := dtoc(cDate1)  
   cDate2  := dtoc(cDate2)      
   cDate3  := dtoc(cDate3)  
   cDate4  := dtoc(cDate4)  
   cDate5  := dtoc(cDate5)  
   cDate6  := dtoc(cDate6)  
   cDate7  := dtoc(cDate7)  
   cDate8  := dtoc(cDate8)  
   cDate9  := dtoc(cDate9)  
   cDate10 := dtoc(cDate10)
   cDate11 := dtoc(cDate11)
   cDate12 := dtoc(cDate12)
   cDate13 := dtoc(cDate13)

Using an array, all of the above code can be written as below. It is also not good practice to assign data of the wrong type to variables with Hungarian notation (e.g. assigning date types to variables begining with "c" then changing the type). The problem with doing this is that you never know what type of data the varible holds. Here I have not done that. We have reduced 26 lines of code to 4 lines of code. This is a 80% reduction. You also reduce the possibility of typos which can lead to really hard to find errors.

Code: Select all  Expand view

  dDate99 := ctod("07/01/10")      
   for i = 1 to 13
      cDate[i] := dtoc( dDate99 + 7*i) -1 )
   next

I don't think that 13 weeks is enough. Couldn't it be 14 or 15 weeks if the start date falls on a Sunday and/or the end date falls on a Saturday?

It still seems easier to me to use daily data. Do you have raw daily data?

Regards,
James

[hag]

James:

No daily data avaialable and 13 weeks is a calendar quarter. So changing to 14 or 15 weeks is not an option.
THanks all fo the help.

[James Bott]

Harvey,

No daily data avaialable and 13 weeks is a calendar quarter. So changing to 14 or 15 weeks is not an option.

So you cannot generate certain monthly data without making guesses. For example, if your quarterly data starts on 7/15 then you have no data from 7/1 through 7/14. So, the only possibility is to generate sales for that period using existing data. This may be very inaccurate especially when data is affected by seasons.

My fear is that when you calculate data using guesses, and don't make a very strong point of it when displayed to the users, they will take it as real. I find that this information must be displayed every time the data is displayed or users will forget or new users will not know.

If users are using data that is unrelable to make business decisions, it could have catastrophic effects.

Regards,
James