Defining a table within a data table

[#1]

Hello

Can anyone help me with this? See the attached spreadsheet for details.

I've got a very specific table to build in QV. Each value in the table is calculated differently so a standard table isn't going to work very well (at least I don't think it will), see the green table to see that sort of thing I want. The blue cells show the data behind the table.

What I'd like to do is define the items in the table inside an inline table, see the yellow table.

The rowtext column of the yellow inline table would be used as the dimension in the green table. For each row, the related function in the inline yellow table would be used as the expression to calculate the value in the green table.

Does this make sense? Am I off my trolley? Is there a better way of doing this?

Any help would be appreciated!

Thanks.

[jvitantonio]

Is this useful?

[#3]

Sort of. But the table I want to make will have a lot more values on it, and many of them will be unique. With the table you made imagine having to add "average cost" to item A, but not item B. Or having to add a total average cost field with costs over £50 excluded.

[swuehl]

Seems to me that your yellow table is just defining expressions, I think you can just create a table with no dimension, create an expression per line of your yellow table, label taken from 'rowtext', expression defined by 'function'.

[#5]

To put it another way: is it possible to make the second column of the bottom table (see attached) contain the result of the function, not the function text itself?

Thanks.

[swuehl]

I believe the expression must be defined per chart, so the only way would be to define all possible paths upfront.

You could use a pick/match combination to compare your current dimension value to a list of values and pick the correct expression.

Like

=pick(match(rowtext,'Total Cost','Total Count'),sum(cost),count(item))

If you absolutely need to define that in a table (maybe you need to read the expression definition in from a DB, you could use some script to render a variable that holds the expression.

See also attached.

Hope this helps,

Stefan