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
