Average based variable conditional selection

[#1]

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:

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

[Gysbert_Wassenaar · Accepted solution]

See attached example.

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

[#3]

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