i am new to Qlik-View and struggle with getting a diagramm to work the way it should:
Now i got a table which contains all visiters to our place per year. So what do i want:
1. I want a bar diagramm showing the amount of visiters per year. (so far easy - i just have to define the visiters per year table as a dimension and then as an aggregate-formula i use COUNT (Distinct visiters)
2. The problem is, i need to apply an additional filter. I do not want to count all visiters per year, but only those visiters who have specific values in another column. I figured out how to do this. Instead of defining the year as dimension i defined my own dynamic function as dimension:
=if(VisitorValue = 'XYZ',Visit.Year)
Like i said - so far so good this works. Now the problem is, here we only count the visitors who have the specific value XYZ. But i need to count Visitors with different values. This works like that:
=if(VisitorValue = 'XYZ' OR VisitorValue='ABC',Visit.Year)
3. Ok now the problem: There are like 50 of these Values. So in order to make my construct work i would have to create this HUGE concatenation of terms:
=if(VisitorValue = 'XYZ' OR VisitorValue='ABC' OR VisitorValue='GHJ' OR ... ... ..,Visit.Year)
4. The thing is - ALL VisitorValues which are viable are stored in another table's column. Lets call it: ViableValues. So what i need is a function like this:
Now the question is: Is there are function like this? Or is there in general a more elegant way to solve my problem? I mean in SQL this would be pretty easy and would look like this:
SELECT Visit.Year FROM Visit WHERE VisitorValue in (SELECT ViableValue FROM ViableValuesTable))
The Qlik way is to add a listbox with VisitorValue as field. Then use that listbox to select the VisitorValue values you want to see. If you want you can then add bookmarks to store the selections. And to top it off you can use bookmark names in expressions to make sure the expression always uses the selection in the bookmark regardless of the other selections the user makes. For example count(visiters) would become count( {MyVisitorsBookMark} visiters).
I would create a mapping load of the "ViableValues" in the script, then ApplyMap on your Visitor table to create a flag (1 or 0) field called "ViableValueFlag".
