When using the Prophix business intelligence tool most users want to see the current year YTD total as well as the prior year YTD total as separate columns in the financial, budget and analysis reports. This gives them the ability to compare the point in time performance between the current and previous years. It is very easy to add columns for both of these totals within Prophix reporting through the use of dimension combinations to add Time and Time Perspective dimensions as Columns.
However, you need to update the member selection every time if you want to navigate between each period.
For example, specific months, quarters, or years.
But, here is some good news for you! You can do this with a simple MDX (Multidimensional Expressions) calculation. There are three dimension values in Time Perspective available to you out of the box: Base, QTD, and YTD.
Using these, let’s create a custom time perspective:
- Click Add button
- Enter Key and Name
- Select Custom Version Type then hit OK button
- Enter MDX code below in Calculation Definition
- Click “Check formula syntax” button to make sure that syntax is ok.
Please note that your dimension name may be different than the one used here.
- Click save, and you’re all set!
Now let’s update the data view:
- Move Time dimension to the pages
- Select members for current year
- Add the custom time perspective that you created
- Click OK
Now you can navigate your measures (both current and prior year) by Time dimension as a filter from page.
I hope you found those steps helpful! If you have any questions about Prophix or other business intelligence solutions, please contact us.
Get the Full Gartner Magic Quadrandt for BI Report
Get the full report on the top BI Vendors.Get the Report