As a consultant, I find that the majority of the projects I work on these days involve the need to compare data over a specific time frame. For instance, how have my sales compared year-to-date compared to last year’s year-to-date. While the functionality has always existed in m-Power to accomplish such a task, Calculations now support this functionality built in directly to m-Power. This tech blog is going to illustrate how to use this feature.
Before we dive in, I want to point out that this feature is only available in Report and Summary templates. This was done because this functionality is most suitable for totaling. Additionally, this feature requires the use of a true date field. If you utilize numeric or character dates, please convert them first (either by a UDF, a previous calculation, a date transportation table, etc…).
Creating a Year-To-Date Calculation
Let’s create a new report, I’ll select the fields “Product Description”, “Ship Date”, and “Sales Amount”. Since I want a yearly comparison on product, I will use Product Description as Dimension key (and choose the option to sub-total by Product Description). Once that is done, head to the Calculated Fields in the Data Model. Create a new calculation, and in the Calc Type dropdown select “Year to Date”.
As with other numeric calculations, add a description and set the decimals accordingly.
- Since we want a YTD calculation, leave the “Date Range Type” as the default.
- Be sure “Apply to Field” is set to the value you want totaled/computed.
- In the “Date Field” dropdown, please select your true-date field. If no fields are available, you have not selected a true-date field for this application.
- In the Number of Years back, set the value here for how many years back to examine. For instance, 0 would look at the current YTD. 1 would look at last year’s YTD, etc…
- If your organization utilizes a fiscal year that is different than the traditional calendar year, be sure to specify the first day of your calendar year. For instance, if your fiscal year begins July 1st, you would specify 07/01.
Once completed, you will be taken back to the “Calculation Review” screen. From here, I would recommend copying the calculation you just created. Next, edit the newly copied application. Be sure to modify the calculation description, usually from “Current Year Sales” to something like “Previous Year Sales.” Most importantly, modify the “Number of Years Back” value from 0 to 1. After saving, you will be returned to the “Calculation Review” screen once again.
Notice in the above calculation, the only difference beyond the description is the 3rd parameter listed (the # of years back). As a bit of house keeping, I would recommend, hiding the original field (In my case, Sales Amount) as well as your date field within Field Settings. When you run your application, it will look something like this:
At run time, m-Power automatically detects today’s date, then applies the necessary date ranges to compute the correct values.
Creating a Number of Days Back Calculation
While YTD and MTD behave similarly to one another, the other option a developer has is creating a “Number of X Days” calculation.
To create, follow the instructions set out above, except select “Last x Days” from the “Date Range” dropdown. You will still need to choose the “Apply to Field” and “Date Field” values. However, you will need to select the number of days back to analyze as well as the number of years back. Typically, a common approach would be creating 3 calculations (30 day history (this year), a 30 day history last year, and a 30 day history from 2 years ago) view. A useful aspect of this report is that it will allow you to easily compare the exact same time periods over distinct years, leveraging key business trends.
No matter which option you choose, be mindful that each different grouping of time (column) requires its own calculation.