Using variable in set analysis

[yzhang88]

Hi all,

I am trying to store the selected values in a variable, then use the variable in set analysis.

Method 1:

step1: create variable vMonth=GetFieldSelections(Months in List box),

step2: in set analysis expression, I use =sum({<Date={"$(vMonth)"}>}sales);

Method 2:

step1: create variable vMonth==concat(distinct (Months in List box), chr(39)&', '&chr(39)),

step2: in set analysis expression, =sum({<Date={'$(vMonth)'}>}sales);

Neither of these two methods don't work with variables. If I use GetFieldSelection or Concat directly in set analysis expression , both are working.

=sum({<Date={"$(=GetFieldSelections(Months in List box))"}>}sales)

=sum({<Date={'$(=concat(distinct (Months in List box), chr(39)&', '&chr(39)))'}>}sales)

Not sure if I miss anything, any suggestions? Thx.

[yzhang88]

Thanks for your suggestion. Love this community! My real purpose is passing the selected values from Source A to a dimension in Aggr function for Source B. Is that possible?

Avg(Aggr(sum(sales), Date)).

Can I assign the Date value with variable?

[sunny_talwar]

I am not sure I understand what the real purpose is... would you be able to elaborate a little may be with an example?

[yzhang88]

For example, in one sheet, I have data source A with EmployeeName, Date, other HR info, Source B has EmployeeName, Date, Sales Data. Users pick employeeName and date from Source A filter, I want to show mixed results of HR and avg month sale. 

These two data sources can only join with Employee Name and date, no other relations, it will cause duplicate records using SQL or Qlik Data model.