2 Replies Latest reply: Mar 6, 2012 5:49 AM by Robert Webb RSS

    Breakout Year to Date (YTD) into Months at Load

      Hi

       

      My company distributes Excel file that show YTD Income Variance vs a Prior Year. We receive back files that contain the analysis for every client in our business of why we have more or less revenue than the previous year.

       

      The challenge I have is we need to be able to break this data out in months to track the monthly movement of data by each client in the file.

       

      Each row in the file represents a client and numerous columns represent the variance analysis.

       

      Example

       

      Jan - YTD

      Code        Name       New Busines           Lost          Growth

      A0010      Smith       100

      X0010       Jones                                      -50

      M0010      Brown                                                        20

       

       

      Feb  - YTD                  

      Code        Name       New Busines           Lost          Growth

      A0010      Smith       120

      X0010       Jones                                      -70

      M0010      Brown                                                        80

       

      Required Format - after guidance :-)

       

      Jan - Month

      Code        Name       New Busines           Lost          Growth

      A0010      Smith       100

      X0010       Jones                                      -50

      M0010      Brown                                                        20

       

       

      Feb  - Month                  

      Code        Name       New Busines           Lost          Growth

      A0010      Smith       20

      X0010       Jones                                      -20

      M0010      Brown                                                        60

       

      All past wisdom has suggested doing this at load so that my data comes through as required rather than having to manipuate through formula within the QV. Please note that this is a simplistic view of the data as you can imagine.

       

      Thanks in advance for any support and guidance offered.

       

      Rob