topic Re: Specific value from within a dimension in QlikView

Specific value from within a dimension

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Specific value from within a dimension

Gysbert_Wassenaar — Mon, 25 Apr 2016 16:47:22 GMT

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.

Re: Specific value from within a dimension

Tue, 26 Apr 2016 06:26:19 GMT

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 🙂

Re: Specific value from within a dimension

Gysbert_Wassenaar — Wed, 27 Apr 2016 13:57:39 GMT

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

Re: Specific value from within a dimension

Wed, 27 Apr 2016 19:12:49 GMT

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

Re: Specific value from within a dimension

Gysbert_Wassenaar — Thu, 28 Apr 2016 16:15:12 GMT

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

Re: Specific value from within a dimension

Sun, 01 May 2016 16:43:27 GMT

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! 🙂

Re: Specific value from within a dimension

Gysbert_Wassenaar — Fri, 10 Jun 2016 15:12:54 GMT

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.

Re: Specific value from within a dimension

Fri, 10 Jun 2016 15:21:29 GMT

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.

Re: Specific value from within a dimension

Gysbert_Wassenaar — Fri, 10 Jun 2016 15:25:24 GMT

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?

Re: Specific value from within a dimension

Fri, 10 Jun 2016 15:30:50 GMT

I'm not sure what you mean - the product has a production date which relates it to the year and a recording date which relates it to the parameters in valuecategory in terms of calendar/time? Thanks for taking your time to help 🙂

Re: Specific value from within a dimension

Gysbert_Wassenaar — Fri, 10 Jun 2016 15:44:57 GMT

I don't see a field production date in your data model. That's what I mean by 'incomplete'.

Re: Specific value from within a dimension

Fri, 10 Jun 2016 18:41:01 GMT

The data model is quite big. So i just showed the fields which i want to be interacting with. Is it too far fetched to get an idea of what i want to do?

Re: Specific value from within a dimension

Gysbert_Wassenaar — Sat, 11 Jun 2016 05:50:24 GMT

Yes, so far it is. If I don't know how the fields are related to each other then I have no idea what you're talking about. If you can't create a simplified, but complete data model then I'm afraid you're on your own. Or you can hire a consultant to help you on-site.