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.

          

      Issue:

      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:

       

      ScreenShot235.jpg

       

      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)

        AND

      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.

        

       

      Regards,

          

      Johan

        • 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.

               

               

              regards,

               

              Johan