Trouble Creating Dynamic chart range

taylorking — Tue, 25 Apr 2017 14:44:42 GMT

Hi,

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!

Thanks,
Taylor

topic Trouble Creating Dynamic chart range in App Development

Trouble Creating Dynamic chart range