Variance Calculations and QTD Formula

svbhagat

Hello,

I’m working on developing a Variance Analysis Module.

I have reviewed the posts on Community and found this training that has been helpful.

I used the below link to develop the Comparison and Variance Modules:

https://help.anaplan.com/anapedia/Content/Modeling/Dimensions/Versions/Variance_Reporting_using_Versions.html

For the Comparison Module – I added a Quarter Value and LY Quarter Value as well. See screenshot for Comparison Module.

For the Variance Calc YTD Module: See screenshot for Variance YTD Calc Module.

1) Orange highlights - The formula is setup similar as the training but I’m not able to pull in any data? What am I missing here?

2) Yellow highlights - I would also like to setup a formula for QTD. I know this is not an option in the Time Settings. What is the best way to calculate QTD?

Thanks

Comparison_Module_70319.png

Variance_YTD_Calc_Module_70319.png

Accepted answers

Misbah

@svbhagat

Ah I see. Here is I think what the issue is

Your source module has Time dimension whereas your target module doesn't have time dimension. Unless you have All Periods enabled in Time setting Anaplan doesn't roll up Time automatically. You will have to tell Anaplan which year to pull from the source module. Two ways to correct this

1. Create a Lookup module with no dimension and insert one line item. Format that line item as Time period. And Use LOOKUP on this line item in your formula. DO NOT use SELECT on any Particular year

2. If your Time range has just one year then you can enable All periods in your Time Range, Anaplan will automatically pull the numbers

Hope that helps

einas.ibrahim

@svbhagat

Of course, it depends on your particular business case but usually, QTD means data in just the current quarter. So in your example, if you are in May then QTD will be just the data in April and May and the function QuarterToDate() will return that data accordingly. If you actually want to get the data from Jan to May then you can HalfyearToDate()

Finally, you can use YearToDate() to get the data from Jan to the current month for the whole 12 months.

For your requirement to be able to get the Actuals until the current date then use forecast after, you can utilize the switchover date feature and use the forecast version. That will accomplish that blended data requirement. However, that won't allow you to compare to pure Forecast (Jan to June) as you described.
Usually, we use a forecast version with a switchover date for the "blended" version and a budget version for the static values that won't be replaced by actuals as the year progresses,

If you really want to use only forecast version for both (blended and forecast) then you can use formulas to obtain the switchover date - or current date - and then use an if statement to return the Actual data before that date and forecast data after.

Hope that helps, let me know if you have any questions

All comments

Misbah

@svbhagat

On Point 1 - Can you please share the dimensionality of your source module and target module

On Point 2 - What is QTD? Let's say if you are in May month and you have 100 in each month until May should the expected value be 300 or 500 in QTD line item.

svbhagat

Hello Misbah,

Thanks for the follow up.

Point 1 - The dimensions are as follows:

Comparison Module has the following:

Variance Calc YTD Module has the following dimensions: It does not include Versions and Time as I didn't see that in the training video. The YTD formula is working its just the Full Year formula is not.

svbhagat

I've just responded on the Point 1 and am following up on Point 2.

Misbah

@svbhagat

Ah I see. Here is I think what the issue is

1. Create a Lookup module with no dimension and insert one line item. Format that line item as Time period. And Use LOOKUP on this line item in your formula. DO NOT use SELECT on any Particular year

2. If your Time range has just one year then you can enable All periods in your Time Range, Anaplan will automatically pull the numbers

Hope that helps

svbhagat

Hello Misbah,

@Misbah

Thank you for that solution. I was able to setup the Lookup for the year.

I wanted to follow up on the question you had related to Point 2: Your question was below:

On Point 2 - What is QTD? Let's say if you are in May month and you have 100 in each month until May should the expected value be 300 or 500 in QTD line item.

For QTD, I want to setup the variance calculation as follows: Say we are in May - I want to be able to compare (YTD Actuals for Jan through May + Forecast of June) vs. (Forecast for Jan through June). This would give me a variance analysis of Q1+Q2 in total. I hope this helps.

I am attaching two screenshots of Time Setting and Date Setting Module that I've setup and wondering what is the best way to utilize this to be able to perform the Quarter variance I analysis I would like to do.

Date_Settings_70604.JPG

Time_Settings_70604.JPG

einas.ibrahim

Finally, you can use YearToDate() to get the data from Jan to the current month for the whole 12 months.

svbhagat

Hello Einas,

@einas.ibrahim

Thank you for this information. It came in handy. I've built out the QTD as mentioned below and I'll be working on building out the other scenarios as needed per our customer needs.

einas.ibrahim

@svbhagat

I'm glad it was helpful.
you might want to mark it as a solution so other community members can find it when they search for a similar topic/issue.

Happy Planning

Quick Links

Can you help?

Unanswered questions

Batch Script
Hello team, I am logging into Anaplan through SSO. I would like to create a batch script to import a file into Anaplan. For the Spoke model, I would like to run an process via a batch script to import data from the Data Hub. Can anyone provide me script for both the condition.
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…