Ignore all selections except all fields from specific tables using Set Analysis

[Amit_B]

Hey,

I'm trying to create a measure that refers only to the max year and is affected by selections of fields from only 3 tables from the entire model.

max year:
The data is from 2015 until today.
If user select year=2022,2023 than max year = 2023.
If user select year=2020,2024 than max year = 2024.

The tables which should affect on the measure:
1. Calendar - Year, MonthID, Qtr, Date, flags,  and more.
2. Stores - StoreID, Name, Location, and more.
3. Products - ProductID, ProductName, Group, UnitCost, UnitPrice, and more.
Other tables are not connected to the rows of the relevant information (Nulls) like EmployeeCode or SupplierCode.

I came up with the following formula but it doesn't work for me:
=Sum({<$(=concat({1< $Table -= {'Calendar','Stores','Products'}>} $Field, ' ,'))> * 1<Year={'$(vMaxYear)'}>}Budget)
When the user select a StoreName, the measure's value doesn't updated according selection.
When the user select an EmployeeCode, the measure's value updated to 0, but it doesn't need to change.
I used this: link.

Please help.

[Lisa_P]

Try changing the first part to this in case you have field names with any special characters or spaces ..

<$(='[' & Concat({1<$Table-={'Calendar','Stores','Products'}>}$Field, '],[') & ']')>

[Amit_B]

didn't help, still acting the same

[tresesco]

@Amit_B , Have a look here : https://community.qlik.com/t5/QlikView-Documents/Ignore-all-selections-except-few-fields-using-Set-A...  if that helps

[Lech_Miszkiewicz]

yes - @Lisa_P has a point - I have tested this on my dummy set and the highlighted part was important in my case:

=Sum({<$(=concat({<[$Table]-={"_di*","Data*","MDM*",'Calendar','Stores','Products'}>} '['&$Field,']=,'))>*1<[Cal Year]={2024}>}revenue)

[Lech_Miszkiewicz]

Ohhh @Amit_B - you want to remove also "1" from here:

=Sum({<$(=concat({<[$Table]-={"_di*","Data*","MDM*",'Calendar','Stores','Products'}>} '['&$Field,']=,'))>*1<[Cal Year]={2024}>}revenue)

so youre expression should be like this:

=Sum({<$(=concat({1< $Table -= {'Calendar','Stores','Products'}>}'['&$Field,']=, ' ,')),Year={'$(vMaxYear)'}>}Budget)

simple as that...

[Amit_B]

I received a n error in set modifier expression.

I tried to fix it but still doesn't work.

[Lisa_P]

Can you send a screenshot of what the Expression editor is showing please

[Lech_Miszkiewicz]

Hi @Amit_B ,

1. I hope you are not copying scripts from community as special characters like single and double quotes are converted, so make sure you type in your syntax by yourself in Qlik expression editor
2. If it works for me (i tested it) - it should work for you..

Cheers

Lech