6 Replies Latest reply: May 15, 2014 10:13 AM by LarryQ _ RSS

    MTD amount to YTD amount/Report formatting solutions?

    LarryQ _

      I have trapped branch MTD data amounts that are displayed in a summary report on a monthly basis by branch. I would like to accumulate those totals in a YTD report by branch. The reports (MTD & YTD) would then be displayed on the same page by their month to be used as comparisons by the user. An example would look something like this with January MTD & YTD on one page and February MTD & YTD on another and so on.

       

      January Report MTD     Page1

       

      BRANCH                    AMOUNT

       

      Branch1                      100

      Branch2                      200

      _______________________

       

      January Report YTD     Page1

       

      BRANCH                    AMOUNT

       

      Branch1                      100

      Branch2                      200

       

      ==================

       

      February Report MTD    Page2

       

      BRANCH                    AMOUNT

       

      Branch1                      150

      Branch2                      250

      _______________________

      February Report YTD    Page2

       

      BRANCH                    AMOUNT

       

      Branch1                      250

      Branch2                      450

       

      I would like suggestions on accumlating YTD amounts as well as how to display both reports on the same summary page. Any help is very much appreciated.

        • MTD amount to YTD amount/Report formatting solutions?
          Olly Bond

          Hello Larry,

           

          You should be able to get pretty close to this using summaries with hidden key fields. How "pretty" do you need the formatting to be? Page breaks might be trickier, but there's a hack using a two pass solution where you export the summary as fixed width text, and can then define a Page Header template in a second model.

           

          Best wishes,

           

          Olly

            • MTD amount to YTD amount/Report formatting solutions?
              LarryQ _

              Hello Olly,

               

              Thanks for responding so quickly.

              I am not really sure what you are wanting me to do so this is something that I will really have to think about and try to do hands on.

              I would like the formatting to be the best that I can get, whatever that may be. Thanks again.

               

              LarryQ

                • MTD amount to YTD amount/Report formatting solutions?
                  Olly Bond

                  Hello Larry,

                   

                  There are some Cumulative Sum measure calculations you can use in the summary.

                   

                  If these don't get you want you want, you might find it helpful to create some calculated fields.

                   

                  Assuming that each row of data has fields like:

                   

                  Branch, Year, Month, Sales

                  A, 2011, 01, $100

                  A, 2011, 02, $150

                  ...

                   

                  then you can create a field called Sales YTD which is just:

                   

                  if(Year=Year();Sales;0)

                   

                  which you can then use as a measure.

                   

                  Best wishes,

                   

                  Olly

                    • MTD amount to YTD amount/Report formatting solutions?
                      LarryQ _

                      Hello Olly,

                      That is a similar report layout but each individual branch is listed only once with a YTD total accumulated from all the previous months MLY branch totals. Each branch would then have that YTD total on that report just right below the MLY report. Therefore I am accumulating totals by each individual branch as well as a total amount for all branches combined.

                      Each page lists the MLY branch report totals as well as the YTD branch report totals for any give month. 

                      I will send you a PDF of the January and February reports to clarify this further.

                       

                      I would then like to know your ideas on how to accumulate each YTD branch total and how to display both the MLY and YTD figures for each month on one page. I currently have 2 summaries. One for MLY and then one for YTD.

                       

                      Your responses are very much appreciated[/U].

                        • MTD amount to YTD amount/Report formatting solutions?
                          Grant Perkins

                          Larry,

                           

                          I quick thought here.

                           

                          If you capture MTD/YTD figures the are fixed data that summarised a set of data as it existed at the tome the report was created. The report program would, presumably, have calculated the numbers form the sum of the detail lines.

                           

                          If you convert the report back to data using Monarch it afford you and your management to investiogate the newly freed data using different filters and gouopings and, liklely, a load of stuff that was simply not readily available in the original report. Monarch's summaries can, usually, recalculate the revised MTD/YTD figures based on the details in the selected subsets and so on.  Of course this could just as well apply to the entire data set too.

                           

                          Having the previous fixed MTD and YTD numbers is likely useful in some form for model/data verification work  - but maybe less useful for further analysis efforts? If you report Detail only goes down to MTD per branch (for example) them the YTD should still be available by calculation.

                           

                          In other words is this  a problems you could step around rather than address head-on?

                           

                           

                          Grant