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

AlexChirkin — Wed, 25 Nov 2020 16:16:04 GMT

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)

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

rubenmarin — Thu, 10 Oct 2019 18:15:40 GMT

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?

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

AlexChirkin — Fri, 11 Oct 2019 07:55:21 GMT

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!

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

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

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

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