Subselection in expression

[#1]

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

[swuehl]

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

[#3]

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,

[swuehl · Accepted solution]

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

or

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

to disregard only the selection in field NAME.

Hope this helps,

Stefan

[#5]

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?

[swuehl]

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