Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
sunny_talwar

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