0 Replies Latest reply: Apr 25, 2017 10:44 AM by Taylor King RSS

    Trouble Creating Dynamic chart range

    Taylor King

      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:

      SNAPSHOTDEPARTMENTOWNERCOUNT_REFERENCEVALUE
      01/04/2017DEPT1OWNER115643£ 13,111,841
      01/04/2017DEPT1OWNER245165£ 32,123,484
      01/04/2017DEPT2OWNER115615£ 34,231,543
      01/04/2017DEPT2OWNER264564£ 23,343,344
      02/04/2017DEPT1OWNER134523£ 9,841,654
      02/04/2017DEPT1OWNER242345£ 21,510,386
      02/04/2017DEPT2OWNER123452£ 38,489,814
      02/04/2017DEPT2OWNER254333£ 18,645,745
      03/04/2017DEPT1OWNER134543£ 40,030,025
      03/04/2017DEPT1OWNER254622£ 23,915,355
      03/04/2017DEPT2OWNER123443£ 28,558,367
      03/04/2017DEPT2OWNER221233£ 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