Specific value from within a dimension

[#1]

Hello, I'm quite new to Qlikview and has startet creating smaller applications in an already set-up database. The issue is that i have a dimension/listbox with different values, if i create a chart for example i can plot:

         =avg({<ValueCategory={'Colour'}>}Value)

Here i can then replace both "Colour" and "Value" with a few other parameters (amount, setpoint etc.), plotting by date for example. This works fine, but if for example i would like to do an expression counting the number of items in "Value" for a specific item in the list and dividing by a specific amount of the items (from Colour for example) without selecting that in the list it self, i get stuck.

I can make it work by doing "=count({<Value={'>100'}>}Value)/count(Value)" but this of changes if i select something else than Colour in the list - is it possible to write it for the specific entry in the list, as "amount of numbers >100 of Colour(Value) / number of colour values"

Hope it makes sense, thanks in advance 

[Gysbert_Wassenaar · Accepted solution]

If you want to ignore selections in one or more fields then add them to the set modifier:

To ignore selections in the Colour field:

=count({<Colour=, Value={'>100'}>}Value)/count({<Colour=,>}Value)

To ignore all user selections you can use {1}:

=count({1<Value={'>100'}>}Value)/count({1}Value)

If that doesn't answer your question then I misunderstood it. I hope that in that case you can explain in more detail or with another example what you want.

[#3]

Thank you very much for the quick response, it's pretty tricky to explain, but the solutions seems to be close to what you gave above.

What i have is a listbox/dimension called: "ValueCategory" in this box i can select colour, amount and so forth - each of the selected categories then has data in them in the form of "Value", "Limit" and "Setpoint"

I then want to write an expression that regardless of the choice in the list/dimension "ValueCategory" returns the number of values (from "Value") found in for example colour, amount or whatever above >100 based on the range of dates i give in another list/calendar selecter, and then divide it by the total number og of values in the same category, again regardless of having selected for example colour in "ValueCategory"

I hope that clarifies it a bit, I find it a quite hard to explain 🙂

[Gysbert_Wassenaar]

Can you post a small Qlikview document that illustrates the problem?

[#5]

Hmm, I'm not sure how i can do that - I merely work in the (rather large) database which already has been created

[Gysbert_Wassenaar]

Perhaps this document helps: Preparing examples for Upload - Reduction and Data Scrambling

[#7]

Tried re-creating it as i see it here, I'm not much for putting up the real data. Hope this clarifies it, it's practically how the setup is - and thank you very much for trying to solve it! 🙂

[Gysbert_Wassenaar]

So you have six tables with only one field each and all the tables are totally unrelated to each other. I don't see how you can calculate anything useful with that kind of data model.

[#9]

I'm starting to see that - but that is what i have. They are related though, as each product is produced a certain year or multiple. The valuecategory then holds all the parameters related to the product and value is then the number for that parameter, and lower and upper limit are the boundaries of this parameter pr. product.

[Gysbert_Wassenaar]

If there is a relationship between product and year then the model you posted is incomplete. Can you post something that is complete and correct?