Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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?
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?
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!