Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

ashouli1
New Contributor II

Finding the avg value of the last 3 weeks:

Hello, I'm new to Qlik and I'm trying to figure out a rather difficult expression to write... I have provided an example of the data below:

I have a table with values taken every day.. I am trying to figure out an expression so that the values shown are calculated as followed, if I choose date 12/28, a new valueB will be calculated by taking the average of the value of the  7, 14, and 21st  day BEFORE the date selected.

I would also like to have that average subtracted from the actual value shown for 12/28 below:

Value B =  Avg of Value (12/7, 12/14, 12/21)

Value C =   Value A - Value B

so that I can result : if( value C > 3.00) ... flag Value A below.

DateValue A
12/75
12/143
12/212
12/288
1 Reply

Re: Finding the avg value of the last 3 weeks:

Should be like this

Value B

Avg({<Date = {"$(='>=' & Date(Max(Date)-21, 'DateFieldFormatHere') '<' & Date(Max(Date), 'DateFieldFormatHere'))"}>} [Value A])

Value C

[Value A] - Avg({<Date = {"$(='>=' & Date(Max(Date)-21, 'DateFieldFormatHere') '<' & Date(Max(Date), 'DateFieldFormatHere'))"}>} [Value A])

Community Browser