MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

How to perform a formula calculation
Goto page Previous  1, 2
 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities
View previous topic :: View next topic  
Author Message
Fab
Beginner


Joined: 15 Nov 2016
Posts: 47
Topics: 6

PostPosted: Thu Nov 24, 2016 5:32 am    Post subject: Reply with quote

Kolosu,
first of all many thanks for all your support, very much appreciated.
I tried your new code, while the code is with no doubts more efficient, formula calculations did not give always the expected results, it doesn't work everytime the code must round the number, here is the evidence:

When I have at 160
Code:
000000000000000000003830

you can see that at column 181 we have an 8 so > 5, and the whole number is > +0, ok, but the formula produces this:
Code:

000,004;

which is not what we expected. 3830 + 1000 = 4830 / 1000 is 4,830 and I guess that the edit mask did the remaining work to give out the 000,004. I'm just guessing here, had not time to make some changes and try again. I will do it as soon as I can.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Thu Nov 24, 2016 9:20 am    Post subject: Reply with quote

Fab wrote:

which is not what we expected. 3830 + 1000 = 4830 / 1000 is 4,830 and I guess that the edit mask did the remaining work to give out the 000,004. I'm just guessing here, had not time to make some changes and try again. I will do it as soon as I can.


Fab,

I am not sure as to why you think it is wrong. As I explained in my very first post in this topic, if the amount field you starting with is a small number you would end up with zeroes. Check this post.

http://www.mvsforums.com/helpboards/viewtopic.php?p=61937#61937

In reality your calculated values is 0.003830 So even if you apply the rounding it is still gonna end up as 0.004 as you wanted only 3 decimals.

Check what the beginning value of the field is and then calculate the formula in excel for that number and compare it against the results.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Fab
Beginner


Joined: 15 Nov 2016
Posts: 47
Topics: 6

PostPosted: Thu Nov 24, 2016 10:09 am    Post subject: Reply with quote

Kolusu,
just checked in EXCEL (have no report to check for this calculated field, so I can only check in EXCEL and try to give an output number near to that, the more near the number will be to the EXCEL number, the best we applied the formula). In input I have:
Code:
77.468,53

So formula is:
Code:
=77468,53*0,01187/100/12*5
(here I'm using *5 because these data is from the last quarter I have elaborated, so September)
This formula gives:
Code:
3,83146438

Now I said I would like to stop at 3 dec, so what I was expecting was 3,831 which would be a great result to me. I apologize for my bad english, I'm doing my best tho Smile
Back to top
View user's profile Send private message
Fab
Beginner


Joined: 15 Nov 2016
Posts: 47
Topics: 6

PostPosted: Thu Nov 24, 2016 10:33 am    Post subject: Reply with quote

I'm just thinking that maybe these number are precise enough just applying the EDIT mask with no further calculations
Code:

000000000000000000002295
000000000000000000003830
000000000000000000005360
000000000000000000228495
000000000000000000002870
000000000000000000002295
000000000000000000024900

assuming that I would like to represent these fields wit 3 decimals, I would have something like this:
Code:

2,295
3,830
5,360
228,495
2,870
2,295
24,900

which is exactly what I see in EXCEL

I will apply your round code to the other 2 fields (divide by +100), in this way I'll end up with 2 rounded decimals. I will apply it soon.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Thu Nov 24, 2016 10:48 am    Post subject: Reply with quote

Fab,

Are you using the exact control cards as shown here?

http://www.mvsforums.com/helpboards/viewtopic.php?p=61956#61956

when I use the same control cards I get
Code:

               3.831,460


So you need to run my control cards as is.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Fab
Beginner


Joined: 15 Nov 2016
Posts: 47
Topics: 6

PostPosted: Mon Nov 28, 2016 3:26 am    Post subject: Reply with quote

Hi Kolusu, to be absolutely sure I copied again your code inside my sort, now this is the code I run:
Code:

000016 //QUADRPOL     EXEC PGM=SORT                             
000017 //SORTLIB      DD DSN=SYS1.SORTLIB,DISP=SHR               
000018 //SYSOUT       DD SYSOUT=*                               
000019 //SORTIN    DD DISP=SHR,DSN=PA6GRUP.MKFO2045.D160713     
000020 //SORTOUT   DD DSN=PMLRUFA.MK.QUADRA.POLIZZE.NEW,         
000021 //             DISP=(,CATLG),                             
000022 //             UNIT=SYSDA,SPACE=(CYL,(1,1),RLSE),         
000023 //*            DCB=(DSORG=PS,RECFM=FB,LRECL=226,BLKSIZE=0)
000024 //             DCB=(*.SORTIN)                             
000025 //SORTWK01     DD UNIT=SYSDA,SPACE=(CYL,10)               
000026 //SYSIN        DD *                                       
//SYSIN        DD *                                                     
  OPTION COPY                                                           
    OUTREC IFOUTLEN=128,                                               
           IFTHEN=(WHEN=INIT,                                           
                  BUILD=(04,08,                                $ FIELD-1
                         C';',                                 $ DELMTR
                         39,01,CHANGE=(9,C'D',C'REDUCED',               
                                         C'N',C'NEW'),                 
                              NOMATCH=(39,1),                           
                         C';',                                 $ DELMTR
                         33,06,                                $ FILED-3
                         C';',                                 $ DELMTR
                         12,02,                                $ FILED-4
                         C';',                                 $ DELMTR
                         15,18,SFF,EDIT=(IIIIIIIIIITTTTTTTT),  $ AMT-1 
                         C';',                                 $ DELMTR
                         40,18,SFF,                            $ AMT-2 
                         EDIT=(SII.III.III.TTT,TT),                     
                       SIGNS=(,-),                                     
                       C';',                                 $ DELMTR
                       58,17,SFF,DIV,+100,                   $ AMT-3
                       EDIT=(SI.III.III.TTT,TT),                     
                       SIGNS=(,-),                                   
                       C';',                                 $ DELMTR
                       75,17,SFF,DIV,+100,                   $ AMT-4
                       EDIT=(SI.III.III.TTT,TT),                     
                       SIGNS=(,-),                                   
                       C';',                                 $ DELMTR
                       128:C';',                             $ DELMTR
                       130:DATE1)),                                 
                                                                     
         IFTHEN=(WHEN=INIT,                                         
        OVERLAY=(140:130,8,Y4T,LASTDAYQ,TOGREG=Y4T,       $ QTR DATE
                 150:144,2,CHANGE=(2,C'03',C'02',         $ 1QTR = 11
                                     C'06',C'05',         $ 2QTR = 08
                                     C'09',C'08',         $ 3QTR = 05
                                     C'12',C'11'),        $ 4QTR = 02
                 160:((((49,18,SFF,MUL,+10000000),                   
                                   MUL,+1187),           
                                   DIV,+120000000000),   
                                   MUL,150,2,ZD),         
                         ZD,LENGTH=24,                   
                                                         
                 190:(((86,18,SFF,MUL,+1000),             
                                  DIV,+1200),             
                                  MUL,150,2,ZD),         
                         ZD,LENGTH=24)),                 
                                                         
         IFTHEN=(WHEN=(10,9,CH,NE,C'REDUCED'),           
         OVERLAY=(160:190,24),HIT=NEXT),                 
         IFTHEN=(WHEN=(181,1,ZD,LT,5),                   
         OVERLAY=(104:160,24,ZD,                         
                      EDIT=(SIII.III.III.III.TTT,TTT),   
                      SIGNS=(,-))),                       
                                                         
         IFTHEN=(WHEN=(181,1,ZD,GE,5,AND,160,24,ZD,GE,+0),
         OVERLAY=(104:(160,24,ZD,ADD,+1000),DIV,+1000,   
                     EDIT=(SIII.III.III.III.TTT,TTT),       
                             SIGNS=(,-))),                 
                                                           
         IFTHEN=(WHEN=(181,1,ZD,GE,5,AND,160,24,ZD,LT,+0), 
         OVERLAY=(104:(160,24,ZD,SUB,+1000),DIV,+1000,     
                       EDIT=(SIII.III.III.III.TTT,TTT),     
                             SIGNS=(,-)))                   
                                                           
//                                                         


this is my output:
Code:

-5----+----6----+----7----+----8----+----9----+----0----+----1----+----2----+---
         46.481,11;           000,33;           000,66;                 005,058;
         77.468,53;           000,55;           001,11;               8.429,212;
        108.455,95;           000,78;           001,56;                 011,801;
      4.620.000,00;           033,28;           066,57;                 502,695;
         58.101,40;           000,41;           000,83;                 006,322;
         46.481,13;           000,33;           000,66;                 005,058;
         46.481,13;           000,33;           000,66;                 005,058;
         46.481,13;           000,33;           000,66;                 005,058;
         46.481,13;           000,33;           000,66;                 005,058;
         46.481,13;           000,33;           000,66;                 005,058;
         46.481,13;           000,33;           000,66;                 005,058;
         61.200,15;           000,44;           000,88;               6.659,081;
        503.545,47;           003,62;           007,25;                 054,790;
        116.202,79;           000,13;           001,67;                 012,644;
         69.721,68;           000,50;           001,00;               7.586,293;
         77.468,53;           000,09;           001,11;               8.429,212;
        178.177,63;           000,21;           002,56;              19.387,203;
        116.202,79;           000,83;           001,67;                 012,644;

I just focused the currency amount values, 1st column is $ AMT-2 and is reported as is from these lines of code:
Code:

  40,18,SFF,                            $ AMT-2
  EDIT=(SII.III.III.TTT,TT),                   
SIGNS=(,-),                                   


the other fields are the following in the code I've attached above. You can see results are (strangely), different from yours. Also the calculated formula (the last column) is also reporting a strange values, but only when round up occurs. Following is a little sample of what I have in EXCEL:
Code:

   46.481,11       3,34      2,298878232     6,69
   77.468,53       5,58      3,83146438     11,16
  108.455,95       7,81      5,364050527    15,62
4.620.000,00     332,87    228,4975        665,74
  503.545,47      36,28     24,9045197      72,56
  116.202,79       1,39      5,747196322    16,74

EXCEL fields are not in the same order as my host output, 3rd column is what we calculate with formula and we have that field like the last one.
What I must change in your code is:
- need to change "REDUCED" and "NEW" strings to my native language
- need to chenge this code to reflect the right coefficient I need (1st quarter = 11, 2nd = 8, and so on...), so modified code would look like this:
Code:

150:144,2,CHANGE=(2,C'03',C'11',
                    C'06',C'08',
                    C'09',C'05',
                    C'12',C'02'),

- considering these data I am comparing are from September and we are now in the last quarter, we would have to change again this code to have the right coefficient I need to compare (just a temp modification of course):
Code:

150:144,2,CHANGE=(2,C'03',C'11',
                    C'06',C'08',
                    C'09',C'05',
                    C'12',C'05'),  $<---- modified

this way I will have the same coefficient I had back in September and can exactly compare results

when I run again your code, with these simple modifications I have these output values:
Code:

   46.481,11;           000,33;           000,66;                 002,299;
   77.468,53;           000,55;           001,11;               3.831,460;
  108.455,95;           000,78;           001,56;               5.364,050;
4.620.000,00;           033,28;           066,57;                 228,498;
   58.101,40;           000,41;           000,83;                 002,874;
   46.481,13;           000,33;           000,66;                 002,299;
   46.481,13;           000,33;           000,66;                 002,299;
   46.481,13;           000,33;           000,66;                 002,299;
   46.481,13;           000,33;           000,66;                 002,299;
   46.481,13;           000,33;           000,66;                 002,299;
   46.481,13;           000,33;           000,66;                 002,299;
   61.200,15;           000,44;           000,88;                 003,027;
  503.545,47;           003,62;           007,25;                 024,905;
  116.202,79;           000,13;           001,67;               5.747,195;
   69.721,68;           000,50;           001,00;               3.448,315;
   77.468,53;           000,09;           001,11;               3.831,460;
  178.177,63;           000,21;           002,56;               8.812,365;
  116.202,79;           000,83;           001,67;               5.747,195;

Now we can easily compare these values with EXCEL values (that apply coefficient 05 in formulas).

Also I would need to adjust formula calculation because, as you can see, results are not the same like EXCEL, and would like to round up the two intermediate values (those we divide by + 100). That's all I guess, sorry for delay in answer I was out of my workplace.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Mon Nov 28, 2016 10:31 am    Post subject: Reply with quote

Fab,

Honestly I am kind of lost with you explanation about how you need to change the constant values. However let me clarify a couple of concepts.

1. We use the Same field at position 160 for both formulas.
Code:

IF REC(39:1) = 'D'                                   
   OUTVAL    = (((FIELD-1 * 0.01187)/ 100)/ 12)* 11)
ELSE                                                 
   OUTVAL    = (FIELD-3 / 12) * 11                   
END-IF                                               


So remember that when you are using FIELD-3 you might NOT get the desired precision as I was only considering 3 decimals.

2. Please ensure that your excel and mainframe data are in the same order and also the flags are a 1 to 1 match.

Ideally I would start with just 1 Or 2 records and then compare it against the excel output and start investigating from there.

If you still want me to diagnose the results, I would need to see the complete sysout messages so that I can verify if you have any installation defaults set differently.
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Fab
Beginner


Joined: 15 Nov 2016
Posts: 47
Topics: 6

PostPosted: Thu Dec 15, 2016 10:08 am    Post subject: Reply with quote

Kolusu,
I apology for delay in answer and final thanks for you kind support. Just to explain myself, I needed to translate "reduced" and "new" into my native language (Italian), that is "decurtate" and "nuove". Also I decided to increase the number of decimals of the formula field and do not make any round about that. Formula calculation is good as it is right now, your code works flawless and output file is perfect. So I just wanted to say thank you once more.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12358
Topics: 75
Location: San Jose

PostPosted: Thu Dec 15, 2016 11:17 am    Post subject: Reply with quote

Fab,

Glad to hear you got job working the way you wanted.
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Utilities All times are GMT - 5 Hours
Goto page Previous  1, 2
Page 2 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group