topic Subselection in expression in QlikView

Subselection in expression

Sat, 03 Dec 2011 11:53:54 GMT

Dear All,

I'm struggling with an expression for a chart. I want to calculate the GRADE (denpends of selected NAME) as percentage of the average of CLASS (A or B).

So: the GRADE of John (7) / Avg GRADE (of CLASS: a)

Much thanks for respons.

Arthur

Subselection in expression

swuehl — Sat, 03 Dec 2011 20:09:25 GMT

If you build a table chart with dimensions NAME and CLASS, I think your expression could look like

= only(GRADE) / avg(total<CLASS> GRADE)

You are not having multiple GRADE value per combination of NAME and CLASS, right?

In number tab, you could format the result as you like (e.g. check percentage).

Re: Subselection in expression

Sun, 04 Dec 2011 11:19:33 GMT

Thanks for your reply.

It partiallu works. But somehow the average of CLASS 'A' is changing when I select a NAME. How can you fix the CLASS average when you select a NAME?

Hi Swuehl,

Subselection in expression

swuehl — Sun, 04 Dec 2011 15:16:37 GMT

To not regard the selection on NAME for this calculation, which will influence the average since the selected set is changing, try

= only(GRADE) / avg({1} total<CLASS> GRADE)

to disregard all selections in any field

= only(GRADE) / avg({<NAME=>} total<CLASS> GRADE)

to disregard only the selection in field NAME.

Hope this helps,

Stefan

Re: Subselection in expression

Sun, 04 Dec 2011 19:36:21 GMT

Fantistic this works - much thanks.

One more question.

I need to select both, NAME and CLASS before this expression works.

Is it possible when I select NAME, that CLASS automatically is selected?

Subselection in expression

swuehl — Mon, 05 Dec 2011 21:04:46 GMT

I think you don't need to select CLASS. In your screenshot, I believe the middle and the right image are showing the same value in the bar, "only" the total value displayed is different. Enable values on data point or hover with the mouse over the bar to show the value of it.

The value 1,35 in your example is the value for expression total, and this will only give the correct result if you select CLASS. I would assume you just don't need to show this value (set to different or deactivate on general tab).

If you do need to show the value at this place, I think you can just use this as expression

= aggr(only(GRADE) / avg({<NAME=>} total<CLASS> GRADE),NAME,CLASS)

so it will evaluate correct even as expression total (at least with a NAME selected, if you have nothing selected, you don't get a value, you would need to state the aggregation expression that should be used in this case to aggregate the values of the aggr() table, maybe put sum() or avg() around aggr()?)

Hope this helps,

Stefan