2 Replies Latest reply: Jul 23, 2013 10:07 AM by Johan Govers RSS

    Average based variable conditional selection

      Hello everybody,


      This is my very first post as being a new user in the community.I have now been experimenting with Qlikview for some time with mixed success. I have found this forum very useful to find answers to challenges that I have been running into.


      Currently I am running into a issue for which I have not found any discussion on the community and therefore I sincerely hope you can help me find a solution.



      Dashboard requires a graph with a average line. The average is to be determined based on the selected year. The expected result here is a average (single field) based on the previous years. Please see below example:




      Explaination of fields in the example

      Field selection: represents the selection the user applies on the data displayed on the left;

      Expected result: the calculation that is expected to be returned based on the selection;

      Expected returned value: the value that will be plotted in the graph.


      All fields discussed reside in the same table.


      I would expect (in words) that the following is to be included in the expression:


      Selected year(s)


      Inverse of the selection of the year < selected year


      Until now I have not been able to solve this in any expression (although help function is quite good) and therefore stuck. I hope you can send me in the correct direction!


      Thanks in advance.






        • Re: Average based variable conditional selection
          Gysbert Wassenaar

          See attached example.


          edit: oops wrong attachment, should be the right one now

            • Re: Average based variable conditional selection

              Thank you for the fast response!


              Either I do not get your answer or I must have explained my issue in a incorrect way.


              Looking at your example it "works" as follows:

              - Selection of date 23/07

              - a distinct count of no. of operations (1) is returned (selection based) and title=58


              In relation to the issue that I have raised this discussion for I expect the following:

              - Selection of date 23/07
                 (this would relate to year in the example above)

              - expected result: average of title = 55,5
                 (average based on selection of the previous day where this would relate to the values for a given year in the example above)


              I have studied the solution you have promoted and done some experimenting based on my experience. I was not able to find the solution.


              I hope I have explained the "issue" better.