topic Re: Using variable in set analysis in QlikView

Using variable in set analysis

yzhang88 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: Using variable in set analysis

olivierrobin — Fri, 04 May 2018 14:26:31 GMT

hello

did you try

$(=vMonth)

Re: Using variable in set analysis

sunny_talwar — Fri, 04 May 2018 14:35:59 GMT

Why don't you try just this

Sum({<Date = p(Months)>}sales)

Re: Using variable in set analysis

yzhang88 — Fri, 04 May 2018 14:40:57 GMT

Thanks Sunny. Either GetFieldSelections or Concat expression work, now I am trying to make the variable method working.

Re: Using variable in set analysis

yzhang88 — Fri, 04 May 2018 14:43:11 GMT

Thank you Robin. I did, but no luck here.

Re: Using variable in set analysis

sunny_talwar — Fri, 04 May 2018 14:45:09 GMT

But p() doesn't work?

Re: Using variable in set analysis

shansundar — Fri, 04 May 2018 14:54:41 GMT

Hi,

You have to use both concat and get field selections.

Because when multiple values are selected values will be displayed like A,B we need make it 'A','B' as the variable result.

Thanks,

Shan S

Re: Using variable in set analysis

yzhang88 — Fri, 04 May 2018 15:03:28 GMT

Hi Sunny, I didn't try, it might work. My focus on this question is trying to make the variable method working, not find workaround solution The P() function might work, in my post, using GetFieldSelections or Concat directly in set analysis expression also work. I am new to Qlik and try to learn more stuff during projects. Thx.

Re: Using variable in set analysis

sunny_talwar — Fri, 04 May 2018 15:06:34 GMT

yzhang88‌ - you are free to try and learn, but p() is not a work around.... what you are trying is work around and that too longer work around which I would recommend not using over p().... If I were new, I would first learn p() and then go onto learning other ways of doing it

Re: Using variable in set analysis

yzhang88 — Fri, 04 May 2018 15:27:18 GMT

Just figure out this issue. I am defining different alternate states in each sheet, so need put state name when creating variables.

Method 1:

step1: create variable vMonth=GetFieldSelections(Months, ',', 100, 'StateName'),

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

Method 2:

step1: create variable vMonth=concat({[AlternateStateName]}distinct (Months in List box), chr(39)&', '&chr(39)),

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

both $(vMonth) or $(=vMonth) working.

Thanks everyone.

Re: Using variable in set analysis

yzhang88 — Fri, 04 May 2018 15:33:32 GMT

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?

Re: Using variable in set analysis

sunny_talwar — Fri, 04 May 2018 15:52:14 GMT

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

Re: Using variable in set analysis

yzhang88 — Fri, 04 May 2018 16:55:51 GMT

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.