Routine for WEEK to MONTH SALES calculation

[Former Member]

Hi,

My requirement is i will recieve input excel file as with few other fields for 1 year:

WEEK  SALES

W1        100

W2        110

W3        120

W4        130

W5        140

W6        150

W7        160

.              .

.              .

W52      150

I have to write a routine to convert week sales data to month sales data. W1 to W5 is considered 1st month and W6 to W9 is considered 2nd month and W10 to W14 is considered 3rd month and so on.

Calculate sum of sales from week to month.

Can anyone help me with routine that can be used?

Thanks

[Former Member]

Hi Riya,

Just have 2 fields in a internal table i.e. SALES and MONTH.

The simplest way of doing this is using COLLECT statement, there is no need to write routine to just sum up the sales values.

Hope this helps.

Thanks,

Tooshar Bendale

[Former Member]

Hi Tooshar,

Suppose,

W1 01.01.2014 to 05.01.2014

W2 06.01.2014 to 12.01.2014

W3 13.01.2014 to 19.01.2014

W4 20.01.2014 to 26.01.2014

W5 27.01.2014 to 02.02.2014

W6 03.02.2014 to 09.02.2014

and so on

Till W5 client is considering month1 data.

And i have been given conversion logic of sales depending on which day last day of month comes.

So: W1 + W2 + W3 + W4 + W5(58% of sales value) will go to first month

rest 42% will go for calculation of sales for next month.

Data will be available in BI Data Source, so i have to write routine to convert to month data and update in BI infocube.

Kindly help me with which routine will surve my purpose.

[Former Member]

Hi Riya,

Step 1 : Get the data

Get a internal table (ITAB) as on the data you are getting.

W1          100

W2          110

so on.

Step 2 : Classify the data monthwise into final internal table

Define a internal table ITAB_FINAL with fields MONTH and SALES as fields

DATA : COUNTER.

LOOP AT ITAB INTO WA.

****** Write

     COUNTER = COUNTER + 1.

IF COUNTER = 5

     WA-MONTH = "MONTH1"

     WA-SALES  = WA_FINAL-SALES * 0.58

     APPEND WA_FINAL TO ITAB_FINAL.

     WA-MONTH = "MONTH2"

     WA-SALES  = WA_FINAL-SALES * 0.42

     APPEND WA_FINAL TO ITAB_FINAL.

     CLEAR COUNTER.

ELSE.

     WA-MONTH = "MONTH1"

     WA-SALES  = WA_FINAL-SALES.

     APPEND WA_FINAL TO ITAB_FINAL.

ENDIF.

ENDLOOP.

Step 3 : Here you have the internal table with the month & sales Simply collect

COLLECT ITAB_FINAL.

Hope this helps.

Thanks,

Tooshar Bendale

[Former Member]

Hi Riya,

            First of all use Function module  'TEXT_CONVERT_XLS_TO_SAP' to convert Excel file to internal table. On how to use it search the discussion board.

Then once you have the data in the internal table write the below logic,

consider you have two fields in your internal table,

SORT it_sales BY week.

LOOP AT it_sales INTO wa_sales.

     IF sy-tabix <=  5.

          wa_new-sum = wa_new-sum + wa_sales-sales.

          wa_new-lv_month = 1.

     ELSE.

          APPEND wa_new TO it_new.

         wa_new-lv_month = wa_new-lv_month +1.

          IF count LE 4.

               IF count EQ 1.

                    wa_new-sum = 0.

               ENDIF.

               wa_new-sum = wa_new-sum + wa_sales-sales.

               count = count +1.

         ELSE.

               APPEND wa_new TO it_new.

               CLEAR count.

          ENDIF.

Hope this helps.

Happy Coding,

Santhosh Yadav

[Former Member]

Hi Riya,

             In the above coding the final table it_new will contain two fields lv_month and sum which is actually month and sum of sales.

Regards,

Santhosh Yadav

[narendar_naidu]

Hi Riya,

• As u said u will recieve a excel file , first bring it into a internal table using ALSM_EXCEL TO ITAB Function Module.
• then from that internal table append  it into another internal table and add the values.

hope this solves ur purpose

regards,

[Former Member]

Hi,

Please anyone can help me with which routine will suffice?

Thanks.