topic Re: Re: Script/Expression Help Required in QlikView

Script/Expression Help Required

Tue, 07 Jan 2014 20:05:54 GMT

Hi!

I’m trying to create a graphic with the following expressions:

Sum of business retributions of the business selected in a input box (Select Business);
Average of business retributions of the benchmark selected in a list box (Select Benchmark).

If user chooses:

Option 1 – I consider only business number 4 and 95;
Option2 – I consider only business number 112 and 95;
Option 3 – I consider retribution of business number 112 and 4;

This a very simple example, but I need to create a benchmark with a lot of lists of business and connect the user choice to my average graphic expression. I think to insert these benchmarks in a list box.

If I want to calculate the average of retribution fields of all my business records, is it correct the use of this simple
expression: =avg(retribuzione+contributi+straordinario+inail)

Moreover,I’d like to create a label to show the business selected and the name of benchmark chosen for the average.

I try to use these variable: “vBusiness “ forbusiness selected and “vBenchmark” for benchmark option selected.

You can find an example attached.

Do you think it’s possible to do?

Any suggestions greatly appreciated.

Andrea

Re: Script/Expression Help Required

jerem1234 — Tue, 07 Jan 2014 21:06:16 GMT

Hi Andrea,

Please look at attached. Instead if using a listbox, I used textboxes for each option. I added actions to each textbox to insert the relative companies to the variable vBenchmark. Also changed the expression for the sum to only look at the business from the input box. Also created a label as the title in chart that shows which company was selected as well as the option.

Also combined your Month and Year fields into one, to only have one dimension (Got jumbled up when using both Year and Month as dimension). If you want, you can create this Year-Month field in your loadscript.

If this doesn't meet your requirements, please explain more.

Hope this helps!

Re: Script/Expression Help Required

Tue, 07 Jan 2014 21:35:18 GMT

Thank you! I really appreciated your help!!

Re: Script/Expression Help Required

Tue, 07 Jan 2014 22:13:04 GMT

Hi Jerem! Do you think it's possible to select the option from a drop-down menù?

I will be grateful if you can send me this information.

Andrea

Re: Script/Expression Help Required

Wed, 08 Jan 2014 12:22:45 GMT

Hi Andrea,

If you want to do it as a drop-down then it might be easiest just to create an artificial dimension to use as a substitute for the options buttons. It might look something like this in your script:

Options:

load * Inline [

Option, azienda

1, 4

1, 95

2, 112

2, 95

3, 112,

3, 4

];

If you use a multibox with the new Option dimension then you can get it as a drop-down option.

Hope this helps.

Tyler

Re: Script/Expression Help Required

jerem1234 — Wed, 08 Jan 2014 14:32:54 GMT

If you want to do it as a dropdown, then you will have do it in the loadscript as tmullerric has done. So when you select say Option 1 in the dropdown, it is linked to the 2 companies, 4 and 95. Then you will have to remove the set analysis for your expression of the average (that contains the VBenchmark variable that I added), and create a listbox with Option as field.

Re: Script/Expression Help Required

Wed, 08 Jan 2014 19:27:31 GMT

Hi! Could you be so good as to show me how to do it with an example, please?

Thank you so much!

Re: Re: Script/Expression Help Required

jerem1234 — Wed, 08 Jan 2014 20:06:48 GMT

Sure, take a look at this example.

Hope this helps!

Re: Script/Expression Help Required

Thu, 09 Jan 2014 21:04:42 GMT

Thank you for your help!

Re: Re: Script/Expression Help Required

Wed, 26 Feb 2014 13:50:40 GMT

Hi Jerem!

In your example , how can I count the numbers of "azienda" fields of the Option selected?

Thank you for any help you can provide in this situation.

Andrea

Re: Script/Expression Help Required

jerem1234 — Wed, 26 Feb 2014 13:57:28 GMT

count(distinct azienda) should do it. The azienda field is linked with the Options, so a simple count should do.

If your question for this discussion has been answered, could you mark any posts that were correct/helpful? Just so we can close this discussion and so it may help others that search for the same thing.

Hope this helps!

Re: Script/Expression Help Required

Wed, 26 Feb 2014 19:58:21 GMT

thank you!