One field in two listboxes - different states - how to query?

[datanibbler]

Hil

my scenario is such: There is one field in two listboxes with two different selection_states, say A and B.

In the chart (pivot tablewith two dimensions) on that sheet, there are two sums, each of which has a set_expression specifying that the selection in one of the fields (the state is specified) should be considered.

The code in the set_expression looks like this >> {<[field]=[sel_state]::[field]>} <<

Now I want to display another figure alongside that - but it's in another table in the datamodel.

I can query the second dimension all right. The thing is, to identify which data from the second table to display, I need to consider a sub-part of the string in one of those fields - so I need to use Subfield().

<=> That alone does not work because I cannot specify the selection_state - I have never done this.

Can somebody please lend me a hand here - which aggregation_function can I use to be able to use Subfield() inside it (to query the selection in that field)?`

Thanks a lot!

Best regards,

DataNibbler

[datanibbler]

Okay,

in a simple listbox, I can do this quite easily by just putting the listbox in the same sel_state as the field that I want to query - only I cannot do that for just one formula in the chart. There I need to specify the sel_state that I want to query inside the formula ...

[datanibbler]

One step further 😉

I can kind of query the subfield of one field in a specific sel_state using the ONLY() function for aggregation which is a prerequisite for set_expressions. Now I can go about constructing a set_expression to display the actual values.

It doesn't yet work in all instances, but I will get there eventually.

[datanibbler]

Hi,

now I face a different issue:

I have the right sum now, dependent on the selection in that one field (one specific sel_state).

The issue is, the pivot_table has subtotals and a Total activated - and those figures display only on those total_lines, disregarding my second dimension. Of course, that second dimension is not available under that name in the second table where my figures come from - I have a similar construct there, but with a different name to avoid links in the datamodel.

I am trying to display the figure on the right dimension_point using an IF-construct or a PICK(MATCH()) - but as soon as I do that, the figures are gone and I have a value of 0 ...