Let set analysis override filter selection

[marcobis]

Hi all, I have a dimension on the first table with this definition:

=Aggr(Only({<ORDERS.YEAR = {2023},ORDERS.WEEK = {3}>}ORDERS.DAY),ORDERS.DAY)

there is a second table on the sheet that instead depends on a filter on ORDER.WEEK dimension.

I need those ORDERS.WEEK on the first table always remains 3 and ignores ORDERS.WEEK selection on the sheet. Any help would be appreciated.

[stevedark · Accepted solution]

Hi @marcobis 

I think that you might be coming at it the wrong way.

As a rule of thumb, it is always better to keep your dimensions without set analysis or any other code.

I think that you should be able to have a dimension of just ORDERS.DAY in both tables and then in the measure apply the set analysis. On the first table you would use:

sum({<ORDERS.YEAR = {2023},ORDERS.WEEK = {3}>}ORDERS.VALUE)

And on the second table you would just have:

sum(ORDERS.VALUE)

In order to test that it is doing what it should do, you can add a second dimension of ORDERS.WEEK to both tables and on the first table it should show just week three, but the second table will show whatever is in the current selection.

Hope that helps.

Steve

https://www.quickintelligence.co.uk/blog/

[marcobis]

Thank you @stevedark, it was so simple, but I was focused on the wrong way 😅

[DutchArjo]

If I may continue on t his subject: I have a table which I want to have filtered on a specific variable. I thought I had it working using a set analysis expression containing AGGR and Only:

=Aggr(Only({1 <[peloton]={'D'}, [gp nieuwe ehd]={'pc ost EHDN'}>} [gp nieuwe ehd]),[gp nieuwe ehd])

but it turns out it only works as long as I do not have made a selection in the 'peloton' variable.

However, I allways want the table to show the values belonging to peloton = 'Staf LBO'  regardless of what is selected else for this field.

I thought using set analysis and the '1' set identifier, I would get what I want but in this case the '1' is not working as I expected?