I have been struggling with this on and off over the last couple days and just keep coming up short, hopefully the Qlik community can help solve this one!
Within my app I have a summary table which has a structure like the below which I will refer to as Table_1:
SNAPSHOT
DEPARTMENT
OWNER
COUNT_REFERENCE
VALUE
01/04/2017
DEPT1
OWNER1
15643
£ 13,111,841
01/04/2017
DEPT1
OWNER2
45165
£ 32,123,484
01/04/2017
DEPT2
OWNER1
15615
£ 34,231,543
01/04/2017
DEPT2
OWNER2
64564
£ 23,343,344
02/04/2017
DEPT1
OWNER1
34523
£ 9,841,654
02/04/2017
DEPT1
OWNER2
42345
£ 21,510,386
02/04/2017
DEPT2
OWNER1
23452
£ 38,489,814
02/04/2017
DEPT2
OWNER2
54333
£ 18,645,745
03/04/2017
DEPT1
OWNER1
34543
£ 40,030,025
03/04/2017
DEPT1
OWNER2
54622
£ 23,915,355
03/04/2017
DEPT2
OWNER1
23443
£ 28,558,367
03/04/2017
DEPT2
OWNER2
21233
£ 21,666,180
The [SNAPSHOT] date from Table_1 is used to derive a number of calendar fields, but for the purpose of my question the only one we need to be concerned with [SNAPSHOT.Calendar.Date].
Using Table_1 I create an area chart with the intended purpose of displaying a trend line of the last 7 days with the below expressions set in the properties:
Dimension:IF([SNAPSHOT.Calendar.Date] >= TODAY()-7 AND [SNAPSHOT.Calendar.Date] <= TODAY(), [SNAPSHOT.Calendar.Date])
Measure:Sum(VALUE)
This successfully gives me an area chart plotted from Today and capturing any records available within the last 7 days.
However, what I now want to achieve is for the area chart to display the last 7 days from MAX([SNAPSHOT.Calendar.Date]) date of the current select date range.
For example if the user was to use the date picker to select a date range of 01/04/2017 to 21/04/2017 I would want the area chart to plot available dates between 14/04/2017 and 21/04/2017.
I'm thinking this is going to require the use of set analysis but I can't for the life of my fathom how to achieve it.
Any help towards tackling this one would be appreciated!
