Variance between versions and years
We have a summary balance sheet that is part of a line item subset
The request that now had is whether can create a variance report to compare time periods, versions. Whilst I have had some experience of doing this as evidenced below, this has only been when directly referencing a line item subset. The request for balance sheet would require picking up "Consolidated Balance" and not sure how to go about this.
As always, any help would be much appreciated.
Answers
-
What challenges are you facing? I saw you looked up version 1 and version 2 and showing the variance. You need to follow the similar approach. Do you have trouble getting data with LIS?
0 -
The one where I have looked up version 1 and version 2 was on a different LIS.
I'm having difficulty picking up the LIS shown in the first screenshot at the Consolidated Balance level shown in the second screenshot - is this possible? Thank you for your help
0 -
@ChrisMcCarthy , Are you trying to bring value where source and target line items has different LIS
If yes then you have to create mapping between two LIS's using finditem(LIS1,Name(item(LIS2))
then you can refer the values by using the mapping lineitem accordingly
Let me know if this helps0 -
Chris - Can you show the pivot again in one screenshot, saying this is module 1 and module 2, and these 2 you have to merge, or something like that, which shows the difference between LIS?
0 -
Sorry, probably me not being clear enough in the details that I provided. It is the same line item subset - the variance picks up the "Pre Consol Value", where as I really need it to pick up the "Consolidated Balance"
0 -
Create a line item right after the "value" and may be call it as value2 and dimension has to be BS LIS, Forecast Version and time scale as Month. Make it number format and formula as COLLECT(). Then you can create line items like Version 1 (value2), Version 2 (value2). Formula has to be Value2[LOOKUP: 'SYS05 Version Selector'.'Version 1'] and similar formula for Version2(value2) line item.
Let me know if it works!
0
