Text box function

[#1]

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?

[vishsaggi]

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

       ,'#.##%'

      )

[marcus_sommer]

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

[trdandamudi]

May be as below:

a)

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

      ,'#.##%'

      )

b)

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

      ,'#.##%'

      )

[#5]

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)

[#6]

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%

[#7]

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

[vishsaggi]

Can you share a sample app to work on please ?

[trdandamudi]

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.

[#10]

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