Need help with set expression

[okoye11]

I am new to qliksense and I'm trying out a scenario as part of my learning. I have two tables Sales(contains Sales_Date) and Target(contains Target_Year and Target_Value). Sales table has values for every day and Target has year level data. I'm trying to create a table visual with both data combined. So the table visual will have the following columns: Sales_Date Target_Value

Since the Target table has year level data, I'm trying to bring Target_Value into the table visual using the below set expression in a measure =Sum({<Target_Year = {$(=Year(Date(Sales_Date)))}>}Target_Value)

This doesn't seem to work and I get no value. The expression editor doesn't throw any error. The next option would be to create a Sales_Year column in the Sales table and associate it with the Target_Year in Target table to get the corresponding Target_Value. But I would like to understand why the above expression doesn't work. Do set expression not work in row level? Any guidance is greatly appreciated.

Thank you.

[rubenmarin]

Hi, set analysis is calculated before the table to reduce the data used in the expressions, so the row value can't be used on set analysis.

I think this can be solved while loading script, when loading sales add a field like: Year(Sales_Date) as Target_Year, and this will link each sales to the target they belong.

If there is already a field linking the tables you may need a composite key, like:

Account &'_'& Year(Sales_Date) as TargetLink // On sales table

Account & '_'& Target_Year as TargetLink // on targets table