topic Use input box with calculated field in QlikView

Use input box with calculated field

alec1982 — Mon, 18 Nov 2013 06:41:36 GMT

Hi Guys,

I have a table with the following fields:

ID Quarter CurrentValue PCT LL

1 3/2013 483 4.31% 483

2 4/2013 483 4.31% 504

3 1/2014 483 4.31% 525

I used the following formula to create a new field named LL as follow

if( ID<>Previous(ID),CurrentValue,peek([LL])*PCT)+ Peek([LL])) ) as LL ( The field is colored red above)

The requirement now is to have the PCT entered by the user into an input box and create a bar chart to show Sum(LL) by Quarter

The above script worked perfect if PCT is a static value on a column on the table. Not sure how can we do the same calculation inside a bar chart while PCT is entered by the user

Let me know your thoughts.. and thxs for help!

Best,

Alec

Re: Use input box with calculated field

Mon, 18 Nov 2013 08:09:47 GMT

Hi,

Follow these steps:

1. Create one variable (suppose "vPCT") then edit your script like this:

if( ID<>Previous(ID),CurrentValue,peek([LL]) * vPCT)+ Peek([LL])) ) as LL

2. Add input box object and set this variable.

I hope this will help you.

Regards,

Ashutosh

Re: Use input box with calculated field

alec1982 — Mon, 18 Nov 2013 12:49:31 GMT

Hi,

Thxs for your help but this doesn't work..

The script is reading vPCT as a field and throwing an error that field not found..

Best,

Alec

Re: Use input box with calculated field

tresB — Mon, 18 Nov 2013 13:10:30 GMT

Try using dollar sign ('$') expansion, like:

$(vPCT)

Re: Use input box with calculated field

Mon, 18 Nov 2013 13:31:53 GMT

Hi,

Sorry for that . my mistake use this

if( ID<>Previous(ID),CurrentValue,peek([LL]) * $(vPCT))+ Peek([LL])) ) as LL

Please let me know after try this.

Regards,

Ashutosh

Re: Use input box with calculated field

alec1982 — Mon, 18 Nov 2013 13:58:58 GMT

Hi,

if( ID<>Previous(ID),CurrentValue,peek([LL]) * $(vPCT))+ Peek([LL])) ) as LL

doesn't work.. it picks up the current value of the variable.

I am wondering if we can do all the calculations made in the script for this field in the expression of the chart.

Re: Re: Use input box with calculated field

Mon, 18 Nov 2013 14:58:16 GMT

hi,

Check this attached file. and let me know.

Regards,

Ashutosh Sharma

Re: Use input box with calculated field

anandathome — Mon, 18 Nov 2013 15:21:43 GMT

You can try "Input field" in script.

1. Declare PCT as inputfield.

2. this will give the user the ability to change PCT values in staraight table.

3. Create a linked object as chart.

Then changing the PCT values in straight table would change the trend in chart as well.

Thanks,

Anand.

Re: Use input box with calculated field

alec1982 — Mon, 18 Nov 2013 17:32:17 GMT

It didn't work..

On your file you have changed the last value of the current Value and on mine it is always the same.

Also, I see that ID "3" got duplicated with the new calculation while we should have only one line for each ID.

Thxs,

Alec

Re: Use input box with calculated field

alec1982 — Mon, 18 Nov 2013 17:32:59 GMT

Hi,

Would you please give me more details on 3.

Thxs,

Alec

Re: Use input box with calculated field

Tue, 19 Nov 2013 04:48:48 GMT

Hi,

If you dont want to use duplicate ID then why you are checking this: "ID <> Previous(ID)" because it showing that there are duplicate IDs and I am not changing only last value, I wrote expression according to your given expression . If your requirement is different then clear with one sample file or image.

Thanks and Regards,

Ashutosh

Re: Re: Use input box with calculated field

Tue, 19 Nov 2013 08:12:46 GMT

Hi,

check this file again and let me know.

Thanks,

Ashutosh