Variance Analysis with dynamic version selection

SudhirY
Contributor

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

SudhirY_0-1614436342871.png

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

SudhirY_1-1614436442144.png

 

This is the list of Versions 

SudhirY_2-1614436535309.png

 

Compare Version General List

SudhirY_4-1614436597523.png

Base Version General List

SudhirY_6-1614436642044.png

regards

Sudhir

2 ACCEPTED SOLUTIONS

Accepted Solutions
JaredDolich
Moderator

Re: Variance Analysis with dynamic version selection

@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. 


Jared Dolich

View solution in original post

rob_marshall
Moderator

Re: Variance Analysis with dynamic version selection

@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

View solution in original post

11 REPLIES 11
JaredDolich
Moderator

Re: Variance Analysis with dynamic version selection

@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. 


Jared Dolich

View solution in original post

SudhirY
Contributor

Re: Variance Analysis with dynamic version selection

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
Contributor

Re: Variance Analysis with dynamic version selection

Hi @JaredDolich  @rob_marshall 

 

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

 

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.

SudhirY_0-1614580919907.png

 

 

Appreciate any help here.

 

regards

Sudhir

rob_marshall
Moderator

Re: Variance Analysis with dynamic version selection

@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

View solution in original post

SudhirY
Contributor

Re: Variance Analysis with dynamic version selection

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
Moderator

Re: Variance Analysis with dynamic version selection

@SudhirY 

 

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

jbrass
Certified Master Anaplanner

Re: Variance Analysis with dynamic version selection

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
Moderator

Re: Variance Analysis with dynamic version selection

@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
Certified Master Anaplanner

Re: Variance Analysis with dynamic version selection

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