Variance Analysis with dynamic version selection

Hi ,

I'm trying to pull specific data based on the version selected by the user. I tried using Lookup and Select but didn't work. Any smart ways to fetch the specific values from the selected Version dynamically?

The table where User can select the Base or Compare Version - this is from the list items

Trying to fetch data from another table for Variance Analysis Calcualtions.

This is the list of Versions

Compare Version General List

Base Version General List

regards

Sudhir

Accepted answers

JaredDolich

@SudhirY

Short answer - I think you'll want to consider using a version list rather than native versions.

But, before you go too far down one path or the other, I would suggest you read @rob_marshall's post on the topic of native version variances using best practices. In this article, Rob also links you to another article written by @DavidSmith that helps you decide if you should use native or a list/module for your versions.

If you're willing, I would start there. If you want to go a different direction, come back to this post and we'll build out a suitable solution for you. Given that your lookup modules are not using native versions, I think you'll find Rob's article illuminating.

Oh, yeah, just a small callout - try your absolute hardest to never combine a SELECT and LOOKUP in the same formula. That basically creates a Cartesian join and is incredibly inefficient.

Anxious to hear what you decide.

rob_marshall

@SudhirY

The example within the article is pulling in one data point, but you can have multiple by using another line item subset to "stage" the data. In this case, you would create a line item subset for all data points (other line items) and then continue with the article.

Rob

All comments

JaredDolich

@SudhirY

Short answer - I think you'll want to consider using a version list rather than native versions.

Oh, yeah, just a small callout - try your absolute hardest to never combine a SELECT and LOOKUP in the same formula. That basically creates a Cartesian join and is incredibly inefficient.

Anxious to hear what you decide.

SudhirY

Thanks @JaredDolich

In the scenario I'm working with there are multiple dimensions and datapoints - it feels like the approach native version variances will complicate the model I'm working.

Is there no other simple way to call a specific data point for a given version dynamically?

regards
Sudhir

SudhirY

Hi @JaredDolich @rob_marshall

I tried the approach provided in the post labelled - Variance Analysis With Native Versions Made Easy.

Unfortunately, it's not fitting my scenario as I can only fetch one data point for the scenario on Variance Analysis, my requirement is to pull four data points (fixed line items) overlayed with the specific variable time period and version as chosen by the enduser.

I'm able to activate LISS Versions Transactions for only one line item of the four required.

Appreciate any help here.

regards

Sudhir

rob_marshall

@SudhirY

Rob

SudhirY

Enjoying the journey so far, I got the values into Collect Module for the second, third and fourth line items above, but strange, I don't see any values in the respective Report Modules. - shows Zero - I'm sure I should be missing something here.

rob_marshall

@SudhirY

I will send you a DM so we can figure this out.

jbrass

Hi All -

Question related to this:

What if my data has to be displayed in different formats? For example, I have a P&L view that has some %s mixed within (such as '% of revenue'). Any suggestions on how to show a variance analysis view given that the collect function can only have 1 format? The other way I've been testing is to setup a list that includes 1) actual 2) forecast 3) variance as non-production items and then effectively pointing to those list items within the formula, however this also is restrictive if I want to add a 4th list item for "Variance %", for example. I think I just wish that the collect function could inherit the formatting of the source module?! Any help much appreciated!

Thanks,
Josh

rob_marshall

@jbrass

Changing formats is a bit difficult as in it can't be done, but in your case, the format really isn't changing as the base format is still a number, as long as you don't want the %'s to show up. What you can do, create a SYS module with the LISS in the Applies To, create a Boolean line item and name it Pct (or something). For the LISS members that are percentages, put a true for them.

In your reporting module, you can write an IF THEN ELSE statement basically saying:

If SYS LISS.Pct then data/100 else data

where data is coming from your collect statement.

Does this help?

jbrass

Thank you Rob. I think this helps in ensuring that the number shown in shown in the correct multiple (i.e., if millions, show in millions, etc...), however I'm still struggling to show that "%" sign if necessary. Please let me know if you come across any reasonable solutions for this.

Thanks,

Josh

rob_marshall

@jbrass

Sadly, I don't think there is a "good" way of doing this, unless you put the % in the column heading instead of at the data level.

Rob

jbrass

Got it, thanks again for looking into this.

Quick Links

Can you help?

Unanswered questions

Predefaulting A Feild in ANAPLAN
Hello Anaplan Community, I have a special requirement from a customer and would appreciate your input. Let me explain the situation: We have a hierarchy with three levels: Level 1, Level 2, and Level 3. Screen 1: Creation of Level 1 Items On this screen, the user selects a team. Based on their selection, an action runs to…
Looking for Anaplan End-Users to take a short survey!
We are looking for Anaplan end-users to provide feedback on their experiences with the Excel add-in. Interested individuals will respond to this 5-minute survey to help us understand personal needs and behavior when using the add-in. The feedback provided by survey takers is essential to the roadmap of Anaplan's products.…
CERTIFICATION BADGES - ANNOUNCEMENT
Anaplan Champions! The Community team just posted this announcement that certification badges may not be showing up on your profile probably until next year. Rest assured, you will get credit once you complete and pass the exams. https://community.anaplan.com/t5/Blog/Badges-Back-Soon/ba-p/123385