{1} not ignoring filter selection, why???

[therealdees]

Hi,

I have a request to show in a straight table some non related measures by row, paired with different dimensions inside a "built dimension". It sounds confusing, but it's actually simple:

I created a dimension that holds the following 'titles': inventory 2023, inventory 2024, coverage 2023, coverage 2024, sold units 2024, sold units 2023 and orders 2024.

Then I calculated the values for each subject and added it to a table, so the dimension itself it's a mix of things that was grouped by sku_id, date_id and store_id.

Everything seems to work fine except the 'orders'. Since it doesn't have a store_id I want to show the same value for every store. The only filters I'll have in the dashboard is for the stores, but even if I set analysis to ignore every filter in this field, it only shows the correct value if no store is selected, otherwise it updates to 0.

I'm not sure what's happening, but I'm using the following expression:

If(cobertura_dimensao = 'Pedido', // 'Order'

Num(Sum({1<mes = {'Jan'}, produto_referencia = $:: produto_referencia, key_produto_sku = $:: key_produto_sku, ano = $:: ano, loja_sigla =, key_cnpj =>} cobertura_medida), '##.##0')
)

produto_referencia = reference code of the product (it groups different SKUs, as it won't consider size and color)

key_produto_sku = product sku

ano = year

loja_sigla = store name

key_cnpj = store id

In the print below you can see "Pedido" (orders) is ok, but as soon as I select a store it goes to 0

This is how the data is structured:

Note that there's no key_cnpj (store id), because there is no store id for an order. Still, filtering a store name will change the value displayed, even tho I'm using 1 in set analysis

Any help is appreciated.

Thanks.

[Scotchy]

Try this ...

If(cobertura_dimensao = 'Pedido',
Num(
Sum({<loja_sigla=, key_cnpj=, mes={'Jan'}>} cobertura_medida),
'##.##0'
)
)

Here's what this expression is intended to do:

• Ignore selections in loja_sigla and key_cnpj by setting them to null in the set analysis (i.e., loja_sigla=, key_cnpj=).
• Maintain selections in mes, which is set to 'Jan'.
• Maintain any implicit selections in produto_referencia, key_produto_sku, and ano since they are not mentioned in the set analysis, meaning the current selections will apply.

The part that might be causing confusion is the $:: syntax, which implies that you want to inherit the current selections for those fields. If cobertura_medida does not relate to loja_sigla or key_cnpj, they should be excluded as shown above.

[therealdees]

Hi Scotchy, thanks for the reply!!

Unfortunately it didn't work. If I select a store it still changes the value to 0.

It's really weird because I agree with your explanation. For some reason it just won't ignore the field. You can see in the beginning of the GIF that the filter field is loja_sigla, just like in the set expression...

I'm actually using a nested IF in the measure to deal with the other dimensions values too, not sure if it could be related, but it goes like this:

If(cobertura_dimensao = 'Cobertura (24)',

Num(
'$(=Sum({<ano = {'2024'}, mes = {'Jan'}, cobertura_dimensao = {'Estoque (24)'}>} cobertura_medida))'
/
'$(=Sum({<ano = {'2024'}, mes = {'Jan'}, cobertura_dimensao = {'Venda (24)'}>} cobertura_medida))'
,'##.##0,0'),


If(cobertura_dimensao = 'Cobertura (23)',

Num(
'$(=Sum({<ano = {'2023'}, mes = {'Jan'}, cobertura_dimensao = {'Estoque (23)'}>} cobertura_medida))'
/
'$(=Sum({<ano = {'2023'}, mes = {'Jan'}, cobertura_dimensao = {'Venda (23)'}>} cobertura_medida))'
,'##.##0,0'),

If(cobertura_dimensao = 'Pedido',

Num(Sum({<loja_sigla = , key_cnpj = , mes = {'Jan'}>} cobertura_medida), '##.##0'),

Num(Sum({1<mes = {'Jan'}, loja_sigla = $:: loja_sigla, key_produto_sku = $:: key_produto_sku, produto_referencia = $:: produto_referencia>} cobertura_medida), '##.##0')

)
)
)

PS: Ignore the behavior of the other month columns, as I'm trying to fix it for January first so I can then apply to the other months.

[therealdees]

I did the most obvious, but least recommended that is loop every store_id and add the same value for each. Then I set analysis to a single store and it works, it doesn't respond to the filters anymore... This is far from the ideal and in this case it worked because there aren't so many rows for orders, but it could be a problem depending on the data size.

I'm still very curious to understand this behavior...

I believe it's related to the fact that there are no store_names available in the filter. If I filter select 'Pedido' and then click on the store filter, every value is gray, which means the data will cancel every filter selection if I click on an unavailable value. Maybe it happens in a similar way when using the set analysis...

If anyone has a tip on this I'd be very thankful.

[therealdees · Accepted solution]

Ok, I found a solution and I think I understand the reason behind this.

It happens when there's no record for such store_id or product_sku, so for some reason it won't ignore the filters and show something else because there's no alternative.

This post helped me out:

https://community.qlik.com/t5/App-Development/Show-zero-for-no-records/td-p/1969549

If I sum Sum({1} 0) to the expression it works as expected.

Sum({1<loja_sigla = , key_cnpj = , mes = , ano = , cobertura_dimensao = {'Pedido'}, produto_referencia = $:: produto_referencia>} cobertura_medida)
+
Sum({1} 0)

[Scotchy]

Looks like you found a solution which is good to hear.

Have a  great day.