12 rolling month Variable

[jmbadu]

Hello experts!

I have a table where where I display details of different product and their respective performance in CY vs PY YTD in sales and quantity. I created to queries for the different period and merged the dimensions.

I want to display the rolling 12 months in quantity for my products for for the last 12 rolling months and the 12 rolling months before. However, my 2 queries are filtered on prompts 202301 to 202310 for "CY' and 202201 to 202210 for "PY". Since the 12 rolling months will cover both period, how do I set up this variable ?

For reference my Month dimension is named "Invoice Month" and "Quantity is Order Line Quantity"

Thank you for your help!

[vishalakshmi · Accepted solution]

Hello Jean,

You need to use the relative date function. Try the below procedure.

-->Open the Universe or report in SAP Business Objects where you're working with the data.

-->Create a new variable for your rolling 12 months quantity. You can name it something like "Rolling 12 Months Quantity."

-->Use the RelativeDate function to calculate the rolling 12 months period. In your case, you need to consider both "CY" and "PY," so you'll have to create two separate calculations and then combine them. Here's the formula for each calculation:

For "CY" (Current Year):

=RelativeDate([Invoice Month];-11)

For "PY" (Previous Year):

=RelativeDate([Invoice Month];-23)

-->In these formulas, you're using the RelativeDate function to go back 11 months for "CY" and 23 months for "PY" to cover a rolling 12-month period. This ensures that the calculation spans both periods.

-->Next, you need to sum the "Order Line Quantity" for each of these calculations within your report. Create a new measure or calculation that sums the "Order Line Quantity" for "CY" and another for "PY" within your rolling 12-month periods.

-->Finally, display these calculations in your report. You should have one column for the rolling 12 months quantity in "CY" and another for "PY."

Now, when you apply your query prompts for "CY" (202301 to 202310) and "PY" (202201 to 202210), the rolling 12 months quantity will correctly calculate and display the data for both "CY" and "PY" periods.

Thanks,Lakshmi.

[jmbadu · Accepted solution]

Hi Lakshmi,

Thanks a lot for your detailed answer. Works like a charm.