Formula

[<Lars Johansson>]

Hi all,

I have a data file containing information about how long a contract has been active and what its estimated volume per year is.
I want to calculate estimated consumed volume based on which months it has been active. I do know that a certain percentage of
estimated volume is consumed per month (see look up table).

My question: how can I, in SPSS, write a formula that will calculate and write estimated consumed volume? See example below.



Data file example

date_from       date_to         Estimated_volume_year   Consumed_volume
200606          200607          20000                   9,73%
200606          200612          25645                   50,17%
200607          200611          12321                   33,69%
200612          200703          12125                   45,76%




Look up table

Month           Share           Month
01              12,45%          JAN
02              11,02%          FEB
03              10,92%          MARS
04              8,88%           APRIL
05              6,56%           MAJ
06              5,11%           JUNI
07              4,62%           JULI
08              5,04%           AUG
09              5,99%           SEPT
10              8,27%           OKT
11              9,78%           NOV
12              11,37%          DEC


Thanks!

Lars

[Richard Ristow]

At 02:38 AM 2/2/2007, <Lars Johansson> wrote:

>I have how long a contract has been active and what its estimated
>volume per year is. I want to calculate estimated consumed volume
>based on which months it has been active. I do know that a certain
>percentage of estimated volume is consumed per month (see look up
>table).

Heating oil, or some such? Heaviest in the winter months.

>Can I, in SPSS, calculate and write estimated consumed volume? See
>example below.
>
>Data file example
>
>date_from       date_to         Estimated_volume_year
>Consumed_volume
>200606          200607          20000                   9,73%
>200606          200612          25645                   50,17%
>200607          200611          12321                   33,69%
>200612          200703          12125                   45,76%

In other words: you want to calculate Consumed_volume as it appears in
the above table, using variables "date_from" and "date_to".
"Estimated_volume_year" does not participate in the computation, using
the lookup table you gave but I'm not quoting here (see code, below).

The following, somewhat crude, is tested; SPSS 14 draft output.
(WARNING: The numbers have SPSS percent formats. SPSS's display of
numbers with percent formats is wrong<*>: if used on calculations, they
will not perform as expected from their display.)

<*>"wrong": A view that has been vigorously criticized philosophically,
from SPSS Inc.
*  .................................................. .
LIST.

List
|-----------------------------|---------------------------|
|Output Created               |03-FEB-2007 16:34:41       |
|-----------------------------|---------------------------|
Date_From Date_To Est_Vol Inpt.Consumed

   200606   200607   20000       9.73%
   200606   200612   25645      50.17%
   200607   200611   12321      33.69%
   200612   200703   12125      45.76%

Number of cases read:  4    Number of cases listed:  4


NUMERIC #FM.SPSS #TO.SPSS (MOYR8).
DO REPEAT INPUT.FORM = Date_From Date_To
          /SPSS.FORM  = #FM.SPSS  #TO.SPSS.
.  COMPUTE #XtrctMO  = MOD(INPUT.FORM,100).
.  COMPUTE #XtrctYR  = (INPUT.FORM-#XtrctMO)/100.
.  COMPUTE SPSS.FORM = DATE.MOYR(#XtrctMO,#XtrctYR).
END REPEAT.

NUMERIC Calc.Consumed(PCT7.2).
COMPUTE Calc.Consumed = 0.

NUMERIC #CURR_DT  (MOYR8)
         #THIS_MO  (F2)
         #CURR_CNS (PCT7.2).

COMPUTE #CURR_DT = #FM.SPSS.
LOOP #MOCOUNT = 1 TO 120 /* 10 years max */.
*  Calculate consumption in current month       ... .
.     COMPUTE #THIS_MO = XDATE.MONTH(#CURR_DT).
*     RECODE makes a good data-in-code lookup table .
.     RECODE  #THIS_MO
          (01 = 12.45)
          (02 = 11.02)
          (03 = 10.92)
          (04 =  8.88)
          (05 =  6.56)
          (06 =  5.11)
          (07 =  4.62)
          (08 =  5.04)
          (09 =  5.99)
          (10 =  8.27)
          (11 =  9.78)
          (12 = 11.37) INTO #CURR_CNS.
.     COMPUTE Calc.Consumed = Calc.Consumed + #CURR_CNS.
*  Trace the computation                        ... .
.     DO IF   $CASENUM EQ 1.
.        PRINT / '  Month ' #CURR_DT
                  ': Consumed' #CURR_CNS '; to date ' Calc.Consumed.
.     END IF.
*  Advance date one month, by old-school method ... .
.     COMPUTE #CURR_DT = #CURR_DT + TIME.DAYS(32).
.     COMPUTE #XtrctYR = XDATE.YEAR (#CURR_DT).
.     COMPUTE #XtrctMO = XDATE.MONTH(#CURR_DT).
.     COMPUTE #CURR_DT = DATE.MOYR(#XtrctMO,#XtrctYR).
*  And, end the loop                            ... .
.     DO IF   MISSING(#CURR_DT) /* Crude error check */.
.        BREAK.
.     ELSE IF #CURR_DT GT #TO.SPSS.
.        BREAK.
.     END IF.


END LOOP.
LIST.

List
|-----------------------------|---------------------------|
|Output Created               |03-FEB-2007 16:34:42       |
|-----------------------------|---------------------------|

   Month JUN 2006 : Consumed  5.11% ; to date   5.11%
   Month JUL 2006 : Consumed  4.62% ; to date   9.73%

Date_From Date_To Est_Vol Inpt.Consumed Calc.Consumed

   200606   200607   20000       9.73%        9.73%
   200606   200612   25645      50.17%       50.18%
   200607   200611   12321      33.69%       33.70%
   200612   200703   12125      45.76%       45.76%

Number of cases read:  4    Number of cases listed:  4
======================================
APPENDIX: Test data (from the posting)
======================================
*  ..........   Test data (from posting)   .......... .
*  date_from     date_to     Estimated_volume_year  Consumed_volume .
DATA LIST LIST
   /Date_From(F6) Date_To(F6)
Est_Vol(F7)            Inpt.Consumed(PCT7.2).
BEGIN DATA
    200606        200607      20000                   9.73%
    200606        200612      25645                  50.17%
    200607        200611      12321                  33.69%
    200612        200703      12125                  45.76%
END DATA.