Modeling

AmruthaB
Contributor

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. 

AmruthaB_0-1622731721943.png

Thanks

 

 

4 REPLIES 4
ChrisHeathcote
Community Boss

Re: Modeling

@AmruthaB 

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.

 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA
ChrisHeathcote
Community Boss

Re: Modeling

@AmruthaB 

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. 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA
AmruthaB
Contributor

Re: Modeling

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
AmruthaB
Contributor

Re: Modeling

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