How to ignore in sheet B user selections maded in sheet A without loosing it in sheet A
Hello,
I have an app with two sheets.
One sheet A, user can select three dimension [Area] (Region), [Destination], and [Department] (Departement).
One sheet B with different table where one table is filtered by only one Area (that's the view the final user would like). My dimension [Area] has multiple data but the final user want to see only 4 areas - It means 4 differrent table.
My column [Destination] is filtered too.
I have attached the sheet B in order you can see how I would like the sheet look like at the end.
My problem is when the user is selecting an[AREA] or a [DESTINATION] on sheet A, all my table on sheet B are filtered by the user selection which is Qlik normal way. But that's not what I want !
I've tried to use set analysis on column but with no success.
Please find below the set expression of each column :
[DESTINATION] : =If(Lower(Destination)<>'bureau' AND Lower(Destination)<>'commerce' AND Lower(Destination)<>'services publics' AND Lower(Destination)<>'industrie' ,Null(),Destination)
[202506-12M] : SUM($(vSLC_12M_AnneeMois))
[202506-12M] : SUM($(vSLC_12M_AnneeMois-Y-1))
Etc...
Hope it's clear ! Not easy to explain in english 😉
It are not set expressions else normal if-loops which seems to be used as calculated dimensions.
A set analysis could adjust the selection state but this feature belongs to aggregations which are only difficult to use in calculated dimensions and require there always an aggr-wrapping.
Nevertheless the required view could be in general provided with set expressions, for example with something like:
This means that dimensions which selections should be ignored could be simply listed as fields and all other dimensions which should be adjusted are also listed but with a right side statement defining any including/excluding. In the example would the -= operator excluding the listed dimension-values. This leads to a NULL result for them which could be automatically hidden by the object setting - or in other words it won't be necessary to restrict the dimension with a condition.
Beside this I suggest to transfer the lower(), left() and similar stuff into the data-model and considering an appropriate grouping of the destination respectively to flag them. It makes the UI much simpler.
I'd say that if you really want the selections for the two different sheets to be totally independent from each other, then alternate states is the preferred way. I mean, that is really what the alternate state feature is all about, and it's convenient because you can assign the state to the sheet directly and let it get inherited down to all the visualizations on the sheet. If you go the set-analysis way, then you can eliminate certain selections, but there will still be parts of the state that are reused. And you'll have to remember to use the set-analysis filter on all expressions which is likely to be quite a lot of work to maintain and error prone.
An alternatives may be not to use variables else fields and providing the possibility to select the wanted values. Such selections could be referenced per p() or e(), like:
... Destination = e(Destination) ...
whereby the selection might in the default be locked, the filter hidden in some way ... and mustn't mandatory go against the normal field else it may come from a separate island-table (which may also contain some categorizing) ...
It are not set expressions else normal if-loops which seems to be used as calculated dimensions.
A set analysis could adjust the selection state but this feature belongs to aggregations which are only difficult to use in calculated dimensions and require there always an aggr-wrapping.
Nevertheless the required view could be in general provided with set expressions, for example with something like:
This means that dimensions which selections should be ignored could be simply listed as fields and all other dimensions which should be adjusted are also listed but with a right side statement defining any including/excluding. In the example would the -= operator excluding the listed dimension-values. This leads to a NULL result for them which could be automatically hidden by the object setting - or in other words it won't be necessary to restrict the dimension with a condition.
Beside this I suggest to transfer the lower(), left() and similar stuff into the data-model and considering an appropriate grouping of the destination respectively to flag them. It makes the UI much simpler.
I've made some tests and find that finally the problem was the definition of my variable 'vSLC_12M_AnneeMois'.
I've tried your example just combining the original measure '#Surf_Loc_Commence_M12' and the set analysis and now the user can select area or destination on sheet A with no impact on my result.
So now my expression is : Sum({<Destination={'Bureau','Commerce','Industrie','Services publics'}, Region_Libelle={'11 - Île-de-France'}, Annee_Mois={$(vAnneeMois)}, Departement_Code-=>}[#Surf_Loc_Commence_M12])
Is it possible to put some part of my set analysis in variable like the destination names for example so if the user want to add another destination later, I just have to change it in my variable and not in each column ? I've tried but with no success, my syntax seems to be wrong.
I'm not sure I follow exactly what you are looking for, but if you don't want selections in sheet A to affect selections in sheet B, then perhaps you want to look into using alternate states? You can find more info here if this is a reasonable solution for you:
I've tried your solution with alternate state and it's working fine too (i wanted to have a sheet B where all previous selection from others sheets have no impact on this one and doesn't change my results)
Now i'm wondering if one method is better or if both are ok (using set expression or alternate state) !
I'd say that if you really want the selections for the two different sheets to be totally independent from each other, then alternate states is the preferred way. I mean, that is really what the alternate state feature is all about, and it's convenient because you can assign the state to the sheet directly and let it get inherited down to all the visualizations on the sheet. If you go the set-analysis way, then you can eliminate certain selections, but there will still be parts of the state that are reused. And you'll have to remember to use the set-analysis filter on all expressions which is likely to be quite a lot of work to maintain and error prone.
An alternatives may be not to use variables else fields and providing the possibility to select the wanted values. Such selections could be referenced per p() or e(), like:
... Destination = e(Destination) ...
whereby the selection might in the default be locked, the filter hidden in some way ... and mustn't mandatory go against the normal field else it may come from a separate island-table (which may also contain some categorizing) ...
Finally i've use both of your solution, alternate state to be indepandant from my first sheet and set analysis in order to fix all my table in second sheet. I have one table by area (only 4 area, not all) and when the user clik on one the area from table 1 all the other areas on the sheet are updated with that area. So all the other table where empty.
Now it's working fine so thank you for you help.
@marcus_sommer for my variable it's ok now too, so thank you
