?
Solved

Reporting Period

Posted on 2013-01-27
6
Medium Priority
?
166 Views
Last Modified: 2013-10-21
Need help to create 12-month running total by customer_id which include the following:
1.  MonthlyTotal
2. Monthly Percentage,
3. Previous Month total,  
3.  YTD
4 . YTD Percentage


Below is an example of what I am trying to accomplish. Within the example there are customer_id , 23 and 24. Each row of data represents one month along with sales amount. The variable I would like to calculate is the "sales_amout", which is the 12 month running total within the customer_id. As you can see, the first 12 "sales_amount" is blank because there hasn't been 12 months of data yet.


this Data table for example is a short list of 12 months of data:

customer_id        Month                    Sales_Amount

23                         01JAN2013

23                         02JAN2013

23                         03JAN2012

24                         01DEC2012            10.00
                                      .
24                         02DEC2012             30.00

24                         03DEC2012             50.00    


Here is my code  and code is not working:
Data TOTAL_SALES; 
Set SALES; /** allows first. and last. processing **/
By customer_id  month prev_month; 

RETAIN total 0 monthTotal 0  month1 – month12  prevmonth_tot 0;

if sales_amount = . then delete;
/* month 1 though  month 12*/
         ARRAY sales_amount {12} $ month1-month12;
         
        IF first.sales_amount THEN do;
           do i = 1 to 12;
              sales(i)='';
            end;
        end;
        n+1;
         sales(n)=sales_amount;
        IF last.customer_id THEN do n=0; output; end;
	       

 
/*Month Total*/
month_total + sale_amount;
total + sale_amount;
total = sum(of month1 – month12);
 total + sales_amount
if FIRST.customer_id then monthTotal = 0      ; 
if LAST.customer_id then output ; 

/*Month Percentage*/
month/month
pct=sales_amount/total


/*previous month*/

Array mths {12} month1 – month12;
do i = 2 to 12;

index1 = month(date); /** extract month from date value **/
mths{index1} = mths{index1} + sales_amount;

if first.customer_id{i} then do prevmonth_tot{i} =0;
if Last.customer_id{i} then output;


/*YTD*/

Array months {12} month1 – month12;

index2 = month(date); /** extract month from date value **/
months{index} = months{index} + sales_amount;
if last.customer_id then do; /** last observation for customer **/
YTD_total = sum(of month1 – month12); /** year-to-date total **/
output; /** output one observation per customer **/
do i = 1 to 12;
months{i} = 0; /** reset to zero for next customer **/
end;
end;


/*YTD* Percentage*/
do i=12;
ytd = (i)/total

Open in new window

Deleted by Netminder, no points refunded:  10/21/2013 8:40:21 PM
http://www.experts-exchange.com/R_24694.html
0
Comment
Question by:dfn48
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
6 Comments
 
LVL 35

Accepted Solution

by:
James0628 earned 2000 total points
ID: 38830165
I'm probably not going to be able to help, because I don't recognize that code, but, FWIW, some more info might be helpful to others, so they don't have to guess/assume.

 What programming language (and maybe db) are you using?

 When you say that your "code is not working", what does it do?  Do you get an error or incorrect results or something else?  If it's incorrect results, what's wrong with them?

 Just trying to help get the ball rolling.

 James
0
 
LVL 35

Expert Comment

by:James0628
ID: 38834370
aikimark,

 Thanks.  I saw that in the "tags", but I haven't used it, so I didn't know if that answered the "code" or db question.  And that still leaves the question about what, exactly, "not working" means.

 James
0
 
LVL 14

Expert Comment

by:Aloysius Low
ID: 38846250
i'm lost tracing thru your code... could you provide the table structure at least of the input table and preferable the output table?
0
 
LVL 35

Expert Comment

by:James0628
ID: 39574325
I doubt that my comment was the solution.  You could just ask to have the question deleted, especially considering how long it's been.

 James
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question