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

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

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.

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.  

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