Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

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

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

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

clipboard_image_0.png

 

'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)

1 Solution

Accepted Solutions
Highlighted

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

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?

View solution in original post

2 Replies
Highlighted

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

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?

View solution in original post

Highlighted
Contributor II
Contributor II

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

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!