How to use in expression data from current year and previous year at the same time?

[AlexChirkin]

Hi, All. Dear experts, I need your help.

I have 3 dynamical expressions. All three are depending of filters values.

'Fact Growth' = FACT - 'Fact YearBefore'

The problem is FACT and 'Fact YearBefore' are in different periods: 'Fact YearBefore' uses the same period as FACT, but year before.

I see 2 ways to solve it:

1) use in 'Fact Growth' expression 'Fact YearBefore' data from previous year period;

2) keep 'Fact YearBefore' data on current year period

my Expressions:

1) FACT:

IF($(vPeriodCalcSelected)='NoCalc',sum({$}$(vINDICATOR_F)),
IF($(vPeriodCalcSelected)='QRT',sum({1 <Start_Date = {">=$(=vLastMQuarterStartDate)<=$(=vLastMQuarterEndDate)"}>}$(vINDICATOR_F)),
IF($(vPeriodCalcSelected)='YTD',sum({1 <Start_Date = {">=$(=vLastMYearStartDate)<=$(=vLastMonthStartDate)"}>}$(vINDICATOR_F)),
IF($(vPeriodCalcSelected)='FY',sum({1 <Start_Date = {">=$(=vLastMYearStartDate)<=$(=vLastMYearEndDate)"}>}$(vINDICATOR_F)),
IF($(vPeriodCalcSelected)='MQT',sum({1 <Start_Date = {">=$(=vLastMFloatQuarterStartDate)<=$(=vLastMonthStartDate)"}>}$(vINDICATOR_F)),
IF($(vPeriodCalcSelected)='MAT',sum({1 <Start_Date = {">=$(=vLastMFloatYearStartDate)<=$(=vLastMonthStartDate)"}>}$(vINDICATOR_F)),
IF($(vPeriodCalcSelected)='QTG',sum({1 <Start_Date = {">$(=vLastMonthStartDate)<=$(=vLastMQuarterEndDate)"}>}$(vINDICATOR_F)),
IF($(vPeriodCalcSelected)='YTG',sum({1 <Start_Date = {">$(=vLastMonthStartDate)<=$(=vLastMYearEndDate)"}>}$(vINDICATOR_F)),
'Custom'))))))))

2) Fact YearBefore:

IF($(vPeriodCalcSelected)='NoCalc',sum({$}$(vINDICATOR_F)),
IF($(vPeriodCalcSelected)='QRT',sum({1 <Start_Date = {">=$(=vLastMQuarterStartDate_YB)<=$(=vLastMQuarterEndDate_YB)"}>}$(vINDICATOR_F)),
IF($(vPeriodCalcSelected)='YTD',sum({1 <Start_Date = {">=$(=vLastMYearStartDate_YB)<=$(=vLastMonthStartDate_YB)"}>}$(vINDICATOR_F)),
IF($(vPeriodCalcSelected)='FY',sum({1 <Start_Date = {">=$(=vLastMYearStartDate_YB)<=$(=vLastMYearEndDate_YB)"}>}$(vINDICATOR_F)),
IF($(vPeriodCalcSelected)='MQT',sum({1 <Start_Date = {">=$(=vLastMFloatQuarterStartDate_YB)<=$(=vLastMonthStartDate_YB)"}>}$(vINDICATOR_F)),
IF($(vPeriodCalcSelected)='MAT',sum({1 <Start_Date = {">=$(=vLastMFloatYearStartDate_YB)<=$(=vLastMonthStartDate_YB)"}>}$(vINDICATOR_F)),
IF($(vPeriodCalcSelected)='QTG',sum({1 <Start_Date = {">$(=vLastMonthStartDate_YB)<=$(=vLastMQuarterEndDate_YB)"}>}$(vINDICATOR_F)),
IF($(vPeriodCalcSelected)='YTG',sum({1 <Start_Date = {">$(=vLastMonthStartDate_YB)<=$(=vLastMYearEndDate_YB)"}>}$(vINDICATOR_F)),
'Custom'))))))))

Fact Growth:

$(mINDICATOR_F_CalcPer) - $(mINDICATOR_F_CalcPer_YB)

[rubenmarin · Accepted solution]

Hi, you can try changing data while loading, add 10000 to %MONTH_ID when loading 'Fact YearBefore' so it will be in the same line as Fact, fact growth will be easier to calulate using this. Is this what you want?

[AlexChirkin]

Hi! Thank you for you answer!  'Fact YearBefore' values shall be calculated  of Fact KPIs values. So I can load Fact KPIs with 1 year offset to new field. 

Looks as good idea. I'll try it.

Thanx a lot, Rubenmarin!