YTD and Prior YTD Measures
Desired Outcomes:
I want to perform month-over-month analysis, comparing prior year-to-date (PYTD) figures with current year-to-date (CYTD) figures, and calculate the difference for each month.
I need the analysis to be scalable, allowing aggregation at different levels of granularity, i.e., quarterly, monthly, and potentially daily.
An example of the outputs:
Background:
I have sales figures, currently aggregated on a monthly basis, with the potential to extend this to daily data, maybe a bit later.
Current Setup:
- I’m using a time intelligence node in the data flow.
- There is a date hierarchy in the model.
For the YTD metric, I’m doing this:
For the PYTD metric, I’m using:
Challenges:
- The YTD and PYTD metrics work correctly only when the Date hierarchy is included in the discovery.
- However, the hierarchy view in the discovery doesn’t display each attribute in a separate column.
- I would like to use these metrics/formulates across different period attributes as well, not just the "Date Hierarchy", but I am not sure how to achieve this.
What am I missing in my current setup? How can I achieve these?
9 replies
-
Hi Daniel,
You can perform this month-over-month analysis by doing the following:
1. Create a report using the date hierarchy and sales
2. Create a previous year formula using the data point in the element block, select the date hierarchy (red box). Then click on the fx formula (yellow box)
3. Select the parallel periods (date year) and add the sales measure (if you don’t select the sales measure then the calculation will work on the selected measure in the discovery) – save the formula
4. Add prev year to the discovery – the prev year reflects correctly in all levels of the hierarchy – year, quarter and month (days too)
5. Create a previous year difference calculation using the following logic: if the date for the previous year is > 0 (there exists a value for the previous year) then the difference = current member(red box) – previous year. For the previous year data point, use the same data point that you used to create the prev year calculation – (green box). Then add the prev year diff calculation to the discovery.
6. Right click on the sales chip in the values dropbox and create a cumulative total
7. Right click on the newly created cumulative sales calculation and click on edit calculation logic
8. From the hierarchies select “By Parent”. This instructs the calculation to accumulate totals per parent, instead of creating a running total (experiment by toggling between parent and none to see the difference in behaviour), click apply.
9. Repeat steps 6, 7, and 8 for both the Prev Year chip and the Prev Year Diff chip. Add the calculations to the discovery and arrange the chips in the order you want to see them
Regards
David
-
Hi Daniel,
Yes, you can achieve this without using a date hierarchy.
1. You will need a parameter to select the required date. Create a Global parameter using full month name
2. Create a Current Month calculation by summing the selected full month name as a member then selecting the MonthYear Parameter (you can copy the current month calculation and adjust the formula as it is using the same member)
3. Create a YTD calculation by using the following aggregate on YTD function and select the MonthYear Parameter (you can copy the current month calculation and adjust the formula as it is using the same member)
4. Create a previous YTD calculation. Copy the YTD function. Instead of selecting the month year parameter, click on the slightly faded fx function on the right hand side of the formula
5. Enter 12 for the lag function. This will subtract 12 months for the selected month to be used as the to date for the year to date for the previous year. Click apply.
6. Create a previous Year Month formula. Copy the current month formula. For the Month Year Parameter click on the fx function (as in point 4) and enter 12 for the lag function. (as in point 5). This will calculate this month last year.
7. Now you can create your discover. Select Sales, then click on date full name, right click date full name then view elements, and show formulations. Select all the calculations.
8. Move the date full month name from filters to columns. Select a month from the 2nd year so you can view previous year calculations.
You can also use any other measures besides sales as the calculations did not specify any measure. You can treat the calculations like any calculations so you can perform subtractions (differences, percentage movement etc).
-
9. And you can set the order of the display of columns by selecting control and clicking on each column, then right click and select query operations, quick sort, custom order, set element order. The select the columns in the order that you want them displayed
-
Hello,
Thank you for the detailed explanation. I am however a little stuck.
1. It looks like we need to create a Model parameter to be "Data Driven", global does not allow me to do that.
2. I think the PYTD will be easier if we can get the YTD to work. I am stuck with getting YTD to work right.
Here is my steps:
2.1 Create Parameter:
The save it as "Month Full Parameter"
2.2 Create the current month formula:
Save it as "Current" :
2.3. Check it from a discovery, and I am not able to use it:
At this point its a little unclear. I tried adding the measure the sum and it yields the same outcome.
The same thing happens when following the steps for YTD.
Could you please let me know if there is anything I might be doing incorrectly up to this point? Or if you need more information on this.
-
-
Hi Daniel,
In order to make the calculation available as a measure (not as a calculated member for the date hierarchy), you must recreate the calculation. You cant amend your existing calculation as it is now defined as a member of the date full month name hierarchy.
Very important to note that when you use the date hierarchy as part of the calculation, Pyramid defaults the context of the calculation to the hierarchy that is used and unless you manually select the measure option, it will be saved as a member of the hierarchy (in this case the date hierarchy).
Select the enable manual selection option and then select measure.
Note that you will have to include the required measure in your formula. If you dont select a measure, Pyramid will use the default measure defined for your model. In this formula you can use the aggregate and ytd functions to define the current year to date calculation.
In your example, you will have chosen the full month name as the hierarchy and the current member (after clicking the blue fx button).
In your discovery, you will use the full month name hierarchy and the sales and current year to date, and previous ytd measures. Note how the calculations appear as measures in the measure tree
-
Hi
We can take a different approach to this.
Essentially a YTD function returns the cumulative value of the measure. starting at beginning of the Year. YTD for January is January's value, YTD for February is January + February, YTD for March is January + February + March, etc. this true at whatever level of granularity you choose, Date, Week, Month, Quarter.
Edit your model and add a new aggregation for the Measure you are interested in, in this case Sales and choose cumulative as the aggregation type. This will create a "semi additive measure", i.e. one that will show a cumulative total when looked at by time, but an additive aggregation on other dimensions. I've called my measure "To Date Sales":
Create a calculated Measure that is the To Date Sales Measure, with Lag (1) for the Year:
You can then create whatever comparison calculation you need between the two. In my case a simple variance.
You can swap in Quarters or Dates for Months and it will calculate the cumulative or YTD totals accordingly.
Hope that helps.
Ian
-
BTW, This cumulative measure can be used in conjunction with Year and other time period filters to show the cumulative total (YTD):
Ian