Modeling
HI, Can someone help the below requirement. Need to get Prior year data, I know to use offset or lag, But for FY, I need to get the data what i have in the prior year (FY) only, when using offset or Lag , I cant use any other summary except "sum". Any help would be highly appreciated.
Thanks
Answers
-
If you target module is not dimensioned by time you can use a LOOKUP function.
Create a systems module to capture all your current time data. Keep the module free of all dimensions including time and create two line items. Format these as time period year.
Name then current year and prior year.
In current year use =PERIOD(CURRENTPERIODEND())
In the prior year line item use =Current Year - 1
Now use the prior year line item to LOOKUP the value from your source module.
If you target module is dimensioned by time period year then create a time settings year module dimensioned only by year.
Create a line item dimensioned by time period year.
Using the following =ITEM(TIME)-1 create a mapping where each year is mapped to the corresponding prior year. Use this to inform your LOOKUP.
0 -
If your target module is dimensioned my time period month then use LAG or OFFSET to calculate the prior year month value and SUM summary setting to aggregate into the Full Year.
If you calculate the individual months correctly to pull through the corresponding months data for the previous year then the SUM of those months will represent prior year for the full year.
0 -
Both my target(I am using lineitem subset) and source has time, for eg, Fy20 = 10 then in FY21 also I need to get 10 only instead of aggregating0
-
Both my target(I am using lineitem subset) and source has time, for eg, Fy20 = 10 then in FY21 also I need to get 10 only instead of aggregating
0
