Variance (Q/Q and Y/Y) in SAP Analytics Cloud

[Naga18]

Hello,

I am facing an issue in reporting wherein we have 5 rolling quarter data in a table. I have added columns to display Q/Q and Y/Y variances. These variances work well for one quarter. However, when I move over to the future quarters, the Q/Q and Y/Y variances do not show correct data (as they are cell referenced) and new Quarter data is displayed after the variance column. Have you encountered this issue and how were you able to overcome?

Here are a couple of screenshots as well. Thank you for your suggestions in advance. 

regards

Nagarajan

[umasaral]

Hi Nagarajan,

you're facing a common issue with dynamic reporting where cell references for Q/Q and Y/Y variances do not automatically adjust as you move to future quarters. Here are some steps you can take to resolve this:

1. Use Relative References: Ensure that your formulas for Q/Q and Y/Y variances use relative references instead of absolute ones. This allows the formulas to adjust automatically when you shift quarters.

2. Dynamic Named Ranges: Consider using named ranges that dynamically adjust to the data range. This way, as you add new quarters, the references update accordingly.

3. OFFSET Function: Utilize the `OFFSET` function in your variance calculations to reference the correct cells based on the current quarter's position.

4. Check Data Table Structure: Make sure your data table is structured consistently, and there are no gaps that might disrupt cell referencing.

5. Testing with Sample Data: Create a small sample table to test your formulas and ensure they behave correctly as you move to different quarters.

By implementing these strategies, you should be able to overcome the issue of incorrect variances and ensure your report updates correctly for future quarters.

[Naga18]

Thank you Umasaral! That helps.

Can you pls send me a help article on how to create relative references to cells? I tried searching SAC Help documentation and could not find one 😞

Thank you again for all your help!

regards

Nagarajan