Fixing dimension after selecting

[JoannaM]

Hi

I have created a dimension. When I select any of the dimenstions from the list, the selected dimension is not visible anymore. How can I make it visible?

My dimension:

=if(match([next OP],'RMDZ','RMSZ'),
aggr(Kombi,[%production order])
,group)

Before selection (column next operation):

After selection (column next operation):

Thanks for your help.

BR, Joanna

[Or]

Do you get a result if you just use the aggr() expression without the surrounding if() statement? This requires there is exactly one Kombi value per [%production order] and that this value is possible for your selection. Kombi is not included in your post so no idea what exactly it is.

Note - when using AGGR() you should generally use an aggregation function. Not using one will result in Only() being applied automatically but it's easier to read if you write out the aggregation function in my opinion.

[JoannaM]

Hi @Or 

Thank you for your answer.

When I leave the surrounding if statement out (=aggr(Kombi,[%production order])) then I still don't get any result. I need the if statement, because I need to get the KB* operation instead of the RMDZ und RMSZ operation step.

When I select for example the KB17 in the next operation column, then 3 filters open:

When I delete the next OP filter, then the values show (column next operation):

So I am guessing it has something to do with the field [next OP]?

Would it be easier to do the formula over the script editor? If so, how would the code be?

PS: Kombi is the grouping I did in script editor: if(match(left("work center",2),'KB','Z1'),"work center") as Kombi.

Thank you for your help.

BR, Joanna

[Or]

After that explanation, I'm a tad confused as to what exactly each field is and what exactly you're trying to achieve...

aggr(Kombi,[%production order]) will return the only Kombi for the matching [%production order]. If there are none, or there is more than one, it will return null. Do you have more than one Kombi for each [%production order]? 

[JoannaM]

The logic is if there is a quantity in position 10 RMDZ then I need to show the Kombi=machine behind the production order. It is basically an offset formula.

Example:

production order 500001539365

RMDZ 20'742kg --> need to show KB17

This all is functioning good, except when I select one of the Kombis in column "next operation". Then the data is gone. All I need is the data to stay when I select something.

I hope this explanation is a bit clearer.

BR, Joanna

[Or]

I'm still not clear - it would probably help if you shared the underlying data structure and what each column in the table is.

Generally speaking, when you make a selection on a calculated dimension, Qlik will make a selection on the fields that are part of the calculation. In your case, that means all three underlying fields are part of the selection. Once the three selections are made, you should have one (and only one) value of Kombi for each production order, but it would seem you have either zero or more than one based on your data structure?

[Kushal_Chawda]

@JoannaM  try below

=if(match([next OP],'RMDZ','RMSZ'),
aggr(only(Kombi),[%production order],[next OP])
,group)

[JoannaM · Accepted solution]

Hi @Kushal_Chawda 

Thanks for your input. With that I found the solution with a slight modification.

Here's the solution:

=if(match([next OP],'RMDZ','RMSZ'),
aggr(only({1}Kombi),[%production order])
,group)

@Or, @Kushal_Chawda 

Thanks a lot for your help!

BR, Joanna