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
Best Answers
-
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.
1 -
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
1
Answers
-
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
Sudhir0 -
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
0 -
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.
0 -
0
-
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,
Josh0 -
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?
0 -
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
0 -
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
0 -
Got it, thanks again for looking into this.
0
