Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
datanibbler
Champion
Champion

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

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

3 Replies
datanibbler
Champion
Champion
Author

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
Champion
Champion
Author

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
Champion
Champion
Author

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