Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Date | Value A |
---|---|
12/7 | 5 |
12/14 | 3 |
12/21 | 2 |
12/28 | 8 |
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])