Variance module using item subsets
Good morning team.
I have a labour model that I am looking to add a variance module to and need some assistance understanding my options. As we have a number of different versions, using native versions for the variance is inefficient and will cause the model to grow much larger than required.
The planning module currently has dimensions forecasting positions, time, versions - there are about 19 line items and only 3 are needed for variance analysis.
My optimal solution would be to create a lineitem subset of the 3 lineitems required and have the lineitems in the variance module become the versions and comparisons between them. I am having trouble achieving this and cannot find reliable information online. from what I can find the collect() function is needed, but that is more used when combining modules which I do not need.
My question is then, what sort of formula can I use to accomplish this as I wont be pointing directly at a line item in the original module. I would simply point my line item in the variance module to the version in the labour module?
Any assistance would be appreciated.
Thank you.
Best Answer
-
Hi @MatthewWilcox ,
My below proposal is with the assumption that your versions are fixed and won't grow in future. If they do, you will have to add more lineitems to accomodate them. And, it's a little not-so-perfect solution, but hope it would give you some idea.
Let's say my DAT01 is dimensioned by Forecasting positions, Time (months) and Versions (I understand it's not native versions in your case though I used native ones for this demo and you can replace them with custom versions) - and I have 6lineitems.
I now created a lineitem subset ('LIS Var Analysis') upon DAT01 to include only 3 of the 6 lineitems.
I now have a 'Var Cal01' module where I 'collect' the original numbers from DAT01 through LIS, and I have separate lineitem for each variance (of every two versions). This would give us 15 lineitems for variances between every 2 of the 6 versions (and hence I called this 'not-so-perfect' solution).
Please note in the above snapshot, that variance lineitems are not dimensioned by 'Versions' as I am using SELECT on required 2 versions for each lineitem.
Now it's upto your requirement of how to pivot and display this on the UX:
Cheers!
0
Answers
-
Hi Mattew,
an example of data would help to understand task better, but from first glance - did you consider trying subsidiary view and formula scope ?
Best,
Konstantin
0 -
Thanks for the response. I have read the links and don't think it quite covers what I am after.
So within my labour planning module (lets call it Dat01) I have 3 line items I am trying to create variance analysis for FTE, Salary and Incentives against all my different versions (Working plan, 3+9,6+6,9+3,actual, budget)
I can create a variance module looking at any single one of those (Cal01 FTE) which would have line items for the versions and formula being 'Dat01'.FTE[Select: VERSIONS.working plan] for each version and additional line items for the variance between them (Working plan vs 3+9) etc. I can create Cal02 Salary and Cal03 Incentive which is achievable.
I am wondering if, instead I can use lineitem subsets to house all of this in a single module so that an end user can easily select at the top of the app page to look at FTE or Salary etc rather than requiring 3 pages (one for each item)
By including the lineitem subset in this new module however, my formula used in cal01 is no longer valid as it would not point directly at FTE in the Dat01 module.
Hopefully that makes more sense.
Thank you.
0
