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

[#1]

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

[fosuzuki]

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

[#3]

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?

[sebastiandperei]

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.

[sebastiandperei]

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

[sebastiandperei]

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%.

[#7]

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

[sebastiandperei]

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

[#9]

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?

[sebastiandperei]

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.