topic Re: How to link input box with predefined values to an expression in a pivot table? in QlikView

How to link input box with predefined values to an expression in a pivot table?

Wed, 22 Aug 2012 20:18:13 GMT

A sample file is attached but I am not sure what I need to do...help me please??!!???!!!?

1. I need to know how to link an input box with predefined values (All; 0%; 50%; 80%; 100% or more) to an expression (% Expended) in a pivot table

All - shows all records regardless of % Expended

0% - shows records that have % Expended equal to 0% or is null

50% - shows records that have % Expended equal to 50% or more

80% - shows records that have % Expended equal to 80% or more

100% or more - shows records that have % Expended equal to 100% or more

2. Last thing I need to do is clear the input box selection and make it default back to All.

Any guidance or assistance would be greatly appreciated!!!!

Re: How to link input box with predefined values to an expression in a pivot table?

fosuzuki — Wed, 22 Aug 2012 22:30:55 GMT

I guess it would be a lot easier if you calculate the %Expended in the script and create a Dimension with it for your user to select it. What do you think?

Fernando

Re: How to link input box with predefined values to an expression in a pivot table?

Wed, 22 Aug 2012 22:48:25 GMT

I am new to QV so I don't how to calculate the % in the script and then link the dimension to the input box????

But, I really need to have the % Expended as the last column. I also have to use a pivot table so that I can add subtotals and show the grouping.

But, maybe if can show me or give me more guidance I can see if it would be feasible?

Re: How to link input box with predefined values to an expression in a pivot table?

sebastiandperei — Thu, 23 Aug 2012 01:15:42 GMT

Hi Nicole,

As I can see, your table only shows one value for each Dimention value.

In that case, you could calculate the "Expended" in script. Then, with only, filter with set analisys.

I've attached the document with corrections in script to calculate the value, and the new expression.

Please, reload and try, because i can't try it wothout the source.

If it doesn't work, please send the copy again.

Re: How to link input box with predefined values to an expression in a pivot table?

sebastiandperei — Thu, 23 Aug 2012 01:16:26 GMT

Anyway, i need to you to send me the reloaded copy, because i forget to correct the rest of the expressions.

Re: How to link input box with predefined values to an expression in a pivot table?

sebastiandperei — Thu, 23 Aug 2012 02:39:52 GMT

Ok, here you have it.

I change expressions to dimentions, because is more easy to filter all the files.

I had to change the possible values of Variable1 too. For this case, will only need numbers, not mixed like "100 or more" or "All". To see all, select 0%.

Re: How to link input box with predefined values to an expression in a pivot table?

Thu, 23 Aug 2012 03:49:17 GMT

Thank you for taking time to help me!

I really understand what you did in the script, but why are there commas now instead of decimals?

Also, the input box doesn't seem to work? 0% should not represent all because it means something else.

I don't have to use the word "All", but I need a word so that the user knows that no selection has been made yet and they can default back to that.

I need the predefined list to reflect the following...

All: shows all records regardless of % Expended

0%: shows records that have % Expended equals to 0% and/or is NULL

50%: shows records that have % Expended equal to 50% or more

80%: shows records that have % Expended equal to 80% or more

100%: shows records that have % Expended equal to 100% or more

I really would have thought there was a way to enter text values in the predefined list and define it so that the table can filter based on the selection? QV seems so complicated!!!!!!!

Re: How to link input box with predefined values to an expression in a pivot table?

sebastiandperei — Thu, 23 Aug 2012 12:02:12 GMT

I haven´t readed right the conditions for each % value.

As I said you, there is a reason for that Variable1 MUST be a number. You can see it in the Set Analysis of expression Only({<Expended={">$(=Variable1)"}>}Expended). How QV could interpret the word "ALL"?? Yes, you must to tell to him.

Make it by variables is not the best option. Why you want to use Varlable1? You have 2 options:

1- The better and most simple is put a list box with the Expended values. Now, you have it in your data, not in the expression, and you could filter as you want (=50%, >20%, =0, =null, etc...)

2- The second, if you don't want to get the all Expended values in this possible selection, you can create a external table(in script), grouping the values you want to show in each "option to show", concatenating differents where clauses. For example:

Show:

Load distinct

Expended,

'All' as Show

Resident Test;

Load distinct

Expended,

'0%' as Show

Resident Test

Where Expended=0 or IsNull(Expended);

Load distinct

Expended,

'20%' as Show

Resident Test

Where Expended>0,2;

...

For most cases, if you can do with fields, is better than using Variables.

QV isn't complicated, we humans are!!!

Re: How to link input box with predefined values to an expression in a pivot table?

Thu, 23 Aug 2012 12:50:42 GMT

I agree humans are definitely complicated, but I am hoping that QV can help make life easier for us.

As you suggested, I have created a list box based on a load inline script, listing my predefined values.

Now I have to understand more about how to limit the records based on the Show code. Can I do this in a trigger or do I need to create a macro?

Re: How to link input box with predefined values to an expression in a pivot table?

sebastiandperei — Thu, 23 Aug 2012 12:58:36 GMT

You don't have to create a inline table.

For first option, just put a list box with the field "Expended" (right click on QV sheet, New object, List Box, and select Expended in Fields)

For second option, put the script I have writed, reload, and make the list box (following the last instructions) with the field Show.

Re: How to link input box with predefined values to an expression in a pivot table?

Thu, 23 Aug 2012 14:01:49 GMT

Yes, I understand but if I do it that way then I will get all of the % Expended Values and I don't want that.

I need to be able to group the values as I mentioned before. I don't want the user to select from any value. I want to categorize them.

If I misunderstood what you were asking then I apologize.

Re: How to link input box with predefined values to an expression in a pivot table?

fosuzuki — Thu, 23 Aug 2012 16:54:17 GMT

Hi,

check the attached qvw.

This solution has one small problem when you collapse the pivot table dimensions, the aggregated %Expended value will not work. It is supposed to work only when the table is fully expanded...

I've used the IntervalMatch function which can be kinda hard to understand for newbies. Let me know if you don't get it ...

Hope this helps

Fernando

Re: How to link input box with predefined values to an expression in a pivot table?

Thu, 23 Aug 2012 16:54:42 GMT

After looking back over your last post, I think I may understand. The second option may work for me. I am working on it now so I will get back with you if it works or doesn't.

Thanks!!!!

Re: How to link input box with predefined values to an expression in a pivot table?

Thu, 23 Aug 2012 16:57:10 GMT

I didn't see an attachment

Re: How to link input box with predefined values to an expression in a pivot table?

sebastiandperei — Thu, 23 Aug 2012 21:48:21 GMT

If it has resolved your question, why don't mark as "resolved"?

Re: How to link input box with predefined values to an expression in a pivot table?

Fri, 24 Aug 2012 11:53:32 GMT

I have marked your answer as helpful, but I still wanted to see if this can be done through the input box?

Thank you for your help I really appreciate it!!!!!

Re: How to link input box with predefined values to an expression in a pivot table?

sebastiandperei — Fri, 24 Aug 2012 14:10:11 GMT

Did you put the script that I sent?

With this you will not need the input box nor the variable. Just select the field Show.

I've attached the solution. In this, you have two ways of filter the table:

- Grouping Show: Using the table Show that was created in script like I sent you

- Expended values: You can choose witch values you want to show.

Im really sorry if my english is not comprensible, Im from Argentina and only "practice" my english in this page!!!

Re: How to link input box with predefined values to an expression in a pivot table?

Fri, 24 Aug 2012 16:22:00 GMT

Yes, I was able to create the list box with no issues just like you did. Thank you.

However, even though I don't need an input box, I wanted it that way. The final report will contain multiple variables in an input box and I was going to add this option there.

Can the Show field be linked to an input box? I will use your suggestion if it is not possible.

Re: How to link input box with predefined values to an expression in a pivot table?

sebastiandperei — Fri, 24 Aug 2012 16:56:17 GMT

Yes.

If you want, use the same variable: Variable1

Check that the input rules has the same values that the field Show.

Go to Document Properties, Triggers, Variable event triggers, find the Variable1 and select it.

Then, click OnChange Add Actions.

Now, Add, and Select in Field.

Field: Show

Search string: =$(Variable1)

Then, when you change the value of Variable1, their value will be selected in Show field.

Re: How to link input box with predefined values to an expression in a pivot table?

Fri, 24 Aug 2012 17:22:51 GMT

I created the trigger for the variable, but when I select the value from the input box it is not working...

I entered the following under Listed Values:

All;0%;20%;50%;80%

When I went back to the properties, it changed to

All;0;0,2;0,5;0,8

What am I doing wrong?