1 Reply Latest reply: Apr 28, 2017 8:46 AM by Peter Rieper RSS

    SUM/AGGR/Set Analysis within Pivot/Chart

    Daniel Young



      This is totally doing my head in!  Please please please help, because for someone who has 6 years on-and-off with Qlikview I'm ashamed I can't yet figure this out myself.  I've scoured the community up and down and read all about set analysis being done once per chart, not per dimension and all that, and I've looked at doing hacks with SUMS with IFs to isolate what I need, but I can't help but think what I want it so simple that it shouldn't need such a complex solution.  I'm up to a day and a half already trying to work out one thing!


      OK, on to the problem which I've attached.


      I have one table with 6 rows representing 6 different months.  These are Jan Feb Mar for 2016 and 2017, exposed as ACTIVITY_MONTH and ACTIVITY_MONTH_COUNTER.  ACTIVITY_MONTH is a date, ACTIVITY_MONTH_COUNTER is an INT which is the equivalent of the month/year but so that I could try doing calculations on ACTIVITY_MONTH_COUNTER - 12 for 12 months ago.


      This demo also has another table with 12 rows, which contains ACTIVITY_MONTH (date) to link to the calendar, ACTIVITY (INT) and some other nonsense filter called SOMEOTHERFILTER (Yes/No STRING) to demonstrate that the sheet in question has quite a few filters for the user to select if wanted which I want to consider.


      All I want to do is display a bar chart with the sum of ACTIVITY for all selected ACTIVITY_MONTH and SOMEOTHERFILTER (easy), with a line going across showing the ACTIVITY for the equivalent month last year.  So if you only select Mar 2017, you'll see one bar for Mar 2017 and one point for Mar 2016.  I want the SOMEOTHERFILTER to be honoured, but I know that Mar 2016 falls outside of the selection so the set analysis has to consist of starting at 1, or somehow starting at $ but setting ACTIVITY_MONTH to something on a row by row basis.


      Many many thanks