5 Replies Latest reply on Mar 5, 2012 5:19 AM by John L

    Month to date and month end numbers on same report

    John L

      I need to report month to date sales as well as month end customer numbers on one report? Is this possible?

       

      My data is by week so for month to date results I can just filter for the weeks in the month and for month end numbers I can just filter on the last week in the month. Not sure how to do both on one report/table though?

       

      Creating a dashboard with two separate reports is possible of course but ideally I want to show both numbers in one table.

       

      Help appreciated.

        • 1. Re: Month to date and month end numbers on same report
          John L

          Does no-one have any ideas re this? There must be some ideas on how to report mixed time dimensions on one table? There was an earlier question like this but I can no longer view the content.

          • 2. Re: Month to date and month end numbers on same report
            Dimitri.B

            John, can you post a sample workbook? Much depends on how you data is structured, especially if your data is already aggregated into weeks as seems to be the case from your description.

            • 3. Re: Month to date and month end numbers on same report
              John L

              Hi Dimitri

               

              Please see attached. Essentially I just want to show the collections for the month (£131k) and the customer numbers for week 52 (1934). We want to automate this report if possible so I was thinking of either adding a relative date filter for month to date or the last week but not sure how to represent both.

              Please note this test twbx is just linking to an Excel spreadsheet. We will be connecting to a proper database once we go live so will add a date table to make things easier.

               

              Any suggestions on the best way to approach this?

              • 4. Re: Month to date and month end numbers on same report
                Dimitri.B

                I think the best way to approach this would be to use custom calculations. One would calculate collections for last month, i.e. MTD, while the other - customers in the last week. Then you won't need to apply any filters, the formulae will take care of it, so you can show both on the same sheet.

                The problem is that your data doesn't really have a proper date dimension, it just has a text field with month names and a number field with week numbers. Any chance you can get proper dates from your data source? If not, then some other auxiliary calculations need to be done to make sure that your "dates" have the right sort order and hierarchy for this approach to work.

                Additionally, what happens if the month ended 2 days into the week? That last week of the month will only have 2 days' worth of the last month and 5 days' worth of the next month.

                1 of 1 people found this helpful
                • 5. Re: Month to date and month end numbers on same report
                  John L

                  Thanks Dimitri. We will create a date table with week numbers linking to fiscal dates and that should help. The month to date numbers would be based on fiscal weeks (4-4-5) rather than actual dates so your last scenario won't apply.

                   

                  Now just have to figure out how to do the custom calculations for month to date and last week