topic Re: Optional Expressions? in QlikView

Optional Expressions?

bdunphy — Fri, 09 Sep 2011 16:35:17 GMT

Hi,

I have a pivot table with 3 expressions.

1) FCast/Actual

2) Prior Year

3) Budget

However not all users need to see all 3 expressions at all times. Sometimes they will only want to see the FCast/Actual - other times they would like to see two expressions.

Is it possible to allow the user to decide what expresssions he/she wants to see?

e.g. only show the FCast/Actual initially and then allow then to select to see the Budget and Prior year too if necessary

If its not possible at the moment - is it something that is planned in version 11?

Thanks

Optional Expressions?

isaiah82 — Fri, 09 Sep 2011 16:38:34 GMT

I believe QlikView 11 will solve for this; see "Conditional Enablement of Expressions and Dimensions" in the 11 Beta Release Notes.

Alternatively you can use a macro to enable/disable expression columns. Do some searching in the Community for this and you'll find a good few posts. -Isaiah

Re: Optional Expressions?

john_duffy — Fri, 09 Sep 2011 19:48:16 GMT

Hello.

Another option could be to create separate charts and apply a conditional expresssion in the layout tab that determines which chart is displayed. In my attached exampe, the user can select Chart Type 1, 2 or 3 in the list box. The selection determines which of the 3 pivot charts is displayed.

John.

Re: Optional Expressions?

Fri, 09 Sep 2011 19:55:42 GMT

Hi,

u could use a variable to contain the field to use in the expression.

I have used and it works.

the expression will be:

sum({$< ................ >} $(variable))

Variable will contain a conditional expression to choose correct dimension decided by the user connected...

C u,

Stefano.

Optional Expressions?

johnw — Fri, 09 Sep 2011 19:59:37 GMT

You shouldn't need version 11 to conditionally enable expressions, though it depends on your chart type. I think only conditionally enabling dimensions is new in version 11.

In a straight table, go to the presentation tab, and click on "conditional" for the column. Enter the condition associated with the column, which will depend on how you want them to select it. You could have a list box and look for the value being possible, or who knows what else.

Re: Optional Expressions?

phaneendra_kunc — Sat, 10 Sep 2011 23:19:08 GMT

see attached for the pivot table expression selector without macros.

Thanks,

Phani

Optional Expressions?

Wed, 21 Sep 2011 08:43:28 GMT

I'm trying to figure out how to use the Conditional enable of expressions in Version 11 but can't find any examples or anything on the forums. The Beta release notes don't have much info yet.?

Anyone used this functionality before?

Re: Optional Expressions?

isaiah82 — Wed, 21 Sep 2011 11:25:18 GMT

beanz wrote:
I'm trying to figure out how to use the Conditional enable of expressions in Version 11 but can't find any examples or anything on the forums. The Beta release notes don't have much info yet.?
Anyone used this functionality before?

It's pretty straightforward; similar to the show/hide of columns you could do on a straight table pre-v11. For each expression (and also dimensions) you simply specify an expression that will enable/disable the given column. There are numerous ways you could use this to control which columns show/hide.

Re: Optional Expressions?

bdunphy — Wed, 21 Sep 2011 11:28:30 GMT

Thanks Guys. Why cant we just have the option of a small dropdown in the caption box to open up a list of checkboxes to enable/disable expressions?

Re: Optional Expressions?

Wed, 21 Sep 2011 12:55:38 GMT

Thanks Isiah,

I've created a variable vShowExpressions and used eg:vShowExpression='Peer Group' to conditionally enable each expression.

I've listed variable values in an input box, so that the user can select a value and it's changes the variable.

But it only allows me to select 1 variable value.

Is there any way to make a listbox with the variable, so that the user can select multiple values?

Re: Optional Expressions?

Wed, 21 Sep 2011 13:47:43 GMT

Any ideas? I think I'm going around in circles.

Re: Optional Expressions?

isaiah82 — Wed, 21 Sep 2011 14:47:35 GMT

Check out the attached as an example (QV11 only) using a control table to select which columns to show. Focus less on the data and more on how I accomplished the dynamic columnns.

Re: Optional Expressions?

Wed, 21 Sep 2011 15:14:12 GMT

Thanks Isiah. This works like a charm.

Re: Optional Expressions?

Thu, 22 Sep 2011 12:40:18 GMT

Hi Phani,

I like your solution.

Is there a way to use an expression like this in the script editor, when you create the inline table:

=Avg({$<Institution= p(Institution2) >} [Actual value])

I tried this :

Metric:

replace LOAD recno() as MetricSequence,* INLINE [

Metric, Expression

Average, "AVG({$<Institution={'*'}>}[Actual value])"

"Peer Group", "Avg({$<Institution= p(Institution2) >} [Actual value])"

];

but it give an error, probably because of the symbols.

Is it possible to have an expression like this in the inline table?

Thanks

Re: Optional Expressions?

phaneendra_kunc — Fri, 30 Sep 2011 20:03:52 GMT

Yes you can write these type of expressions, but not in inline statements.

1.) you can load it from Excel

2.) if you are not using reduce and Reload concept, you can write it in the Grid it self.

a) Edit script

Metric table:

LOAD * INLINE [

Metric, SeqID

Average ,1

Peer Group,2

];

b.) Front end:

Write below this as your expression

pick(SeqID,AVG({$<Institution={'*'}>}[Actual value]),Avg({$<Institution= p(Institution2) >} [Actual value]))

c.) Add Metric as Dimension

d.) Also add Metic as Selector (list box or Multi Box)

Thanks,

Phani

Re: Optional Expressions?

phaneendra_kunc — Fri, 30 Sep 2011 20:04:18 GMT

Yes you can write these type of expressions, but not in inline statements.

1.) you can load it from Excel

2.) if you are not using reduce and Reload concept, you can write it in the Grid it self.

a) Edit script

Metric table:

LOAD * INLINE [

Metric, SeqID

Average ,1

Peer Group,2

];

b.) Front end:

Write below this as your expression

pick(SeqID,AVG({$<Institution={'*'}>}[Actual value]),Avg({$<Institution= p(Institution2) >} [Actual value]))

c.) Add Metric as Dimension

d.) Also add Metic as Selector (list box or Multi Box)

Thanks,

Phani

Re: Optional Expressions?

Thu, 06 Dec 2012 16:20:12 GMT

This is a really great example - thanks!!