topic Re: Text box function in QlikView

Text box function

Wed, 25 Nov 2020 16:16:04 GMT

Hello Experts,

I am working with text boxes in Qlikview and I needed help in two things.

a. How can I add a where clause to the expression in the text box? Below is my expression and I want this expression to work only on the rows where actuals_sa is not blank. Is there a way this can be done?

= num(

avg

(Aggr ( Sum ( actuals_sa ) , calender_date) /Aggr ( Sum ( CPE) , calender_date ) )

,'#.##%'

)

b. Is it possible to detach the text box from some selections while making it responsive to others? Like in my chart I have 4 filters and I want this text box to respond to the selection change of only one of them. Any idea if this can be accomplished?

Re: Text box function

vishsaggi — Wed, 17 Aug 2016 16:53:29 GMT

What is the expression output is it adding the blanks ? What is you expected output for the below expression ?

May be try this:

= num(

avg

(Aggr ( Sum ({< actuals_sa -= '' >} actuals_sa ) , calender_date) /Aggr ( Sum ( CPE) , calender_date ) )

,'#.##%'

)

Re: Text box function

marcus_sommer — Wed, 17 Aug 2016 16:59:18 GMT

Such conditions could be done with set analysis. Here is a very comprehensive explanation: Set Analysis: syntaxes, examples. In your current case try this:

= num(

avg

(Aggr ( Sum ({< actuals_sa = {'*'}>} actuals_sa ) / Sum ( CPE) , calender_date ) )

,'#.##%'

)

- Marcus

Re: Text box function

trdandamudi — Wed, 17 Aug 2016 17:10:59 GMT

May be as below:

Option I:

= num(

avg

(Aggr ( Sum ({< Len(Trim(actuals_sa)) > 0 >} actuals_sa ) / Sum ( CPE) , calender_date ) )

,'#.##%'

)

Option II:

= num(

avg

(Aggr ( Sum ({< actuals_sa = '*' >} actuals_sa ) , / Sum ( CPE) , calender_date ) )

,'#.##%'

)

Say you have 4 fields as filters and you want the text box to respond on Field3 (Filter 3) then you can use as below:

{1<Field3 = $::Field3>}

Example may be as below:

= num(

avg

(Aggr ( Sum (1{< Field3 = $::Field3, actuals_sa = '*' >} actuals_sa ) / Sum ( CPE) , calender_date ) )

,'#.##%'

)

Re: Text box function

Wed, 17 Aug 2016 17:28:25 GMT

Thank you for the reply. The expression right now is calculating the ratio for blanks too, so I am always getting 0 when I perform the Min function. My expected answer is definitely not zero. It should be a percentage value greater than zero.

I tried the expression you mentioned but it gives me some syntax error. The expression does not recognize any variable name after the curvy braces. All variable names appear as normal text (black colour) while in my original expression they did get recognized as variable names (appeared in red colour)

Re: Text box function

Wed, 17 Aug 2016 17:34:29 GMT

Sorry I tried this but does not seem to work. It is still giving me 0% as minimum value whereas the expected minimum value is something greater than 0%

Re: Text box function

Wed, 17 Aug 2016 17:36:10 GMT

Will adding field 3 in the expression detach the text box from all other filters? The text box is a part of a sheet where I have multiple graphs

Re: Text box function

vishsaggi — Wed, 17 Aug 2016 17:50:38 GMT

Can you share a sample app to work on please ?

Re: Text box function

trdandamudi — Wed, 17 Aug 2016 17:57:01 GMT

Is it not that is what you want based on your question. You want it to respond only to one filter instead of multiple filters.

Re: Text box function

Wed, 17 Aug 2016 18:15:24 GMT

Hi Vish.

I am not sure how to share the sample with you. I am very new to Qlikview and have not tried many stuffs in it

Re: Text box function

vishsaggi — Wed, 17 Aug 2016 19:13:59 GMT

You have to go to Use Advance editor link when you reply and attach the sample using attach link. Like below

Re: Text box function

Wed, 17 Aug 2016 19:54:41 GMT

Thanks. Here it is.

Re: Text box function

vishsaggi — Wed, 17 Aug 2016 21:13:38 GMT

I missed adding these curly brackets.

= num(

avg

(Aggr ( Sum ({< actuals_sa -= {''} >} actuals_sa ) , calender_date) /Aggr ( Sum ( CPE) , calender_date ) )

,'#.##%'

)

And can i know where you performing this Min function.

In the dashboard you have sent i can see the percentage value in the Textobject for various selections ?

Any specific selection to be done to replicate the issue please let me know ?

Re: Text box function

susovan — Thu, 18 Aug 2016 06:59:29 GMT

Hi anuashka,

You can by bypass those dimension which you want to do not effect in your expression

Re: Text box function

Thu, 18 Aug 2016 17:10:21 GMT

How do I do that? And the text box is a part of a sheet where I have other charts too and the filters need to work for those charts. Can I bypass certain filter for one specific object?

Re: Text box function

Thu, 18 Aug 2016 17:11:35 GMT

Still seems to have some syntax error.

I am trying to perform the min function in this expression only. So basically I was looking to generate text boxes for three parameters, Min, max and avg values