Set analysis works fine with a single value, in your example "GoalYear=SaleDateY", QlikView does not know with which valie to campare "GoalYear" even though "SalesdateY" has 29 times the same value "2009".
=SUM(if(GoalYear=SaleDateY,GoalQuantity,0)) would give you a value but is not good regarding performance issues.
A solution would be to be able to create a flag in your load script and use the flag to calculate your expression.
Hi Why it doesnt know which values to sum up? Each cell in the pivot corresponds to certain dimensions in the pivot, so what's the difference (from QV point of view) between the expression which you wrote, and the SET ANALYSIS one? Thanks
Syntax seems to be all right, provided SaleDateY contains a year (so it does according to your previous post) and SaleDateM contains a month in the same way (text, number) it is stores in GoalMonth field. If not, set analysis would return unexpected values... What do you have in SaleDateM variable and how do you soter you months in GoalMonth field?
The problem is that SalesDateM has 7 possible values and you are trying to force a selection without the user having made a selection. SaleDateY is going to have the same problem once more than 1 year becomes available. Are you wanting the formula to work accross all possible values? In other words, should GoalMonth select all values in SaleDateM (2, 3, 4, 5, 6, and 7) until a selection is made for one or more months? If so, you have to provide a mechanism for the set expression to see all possible field value through a concatenation.
In this formula, GoalYear and GoalMonth are handed all possible values of SaleDateY and SaleDateM in a comma separated list. Now no matter what is selected in SateDateY and SaleDateM (single OR multiple selections), you will have the formula calculating for all possible values.