topic Re: Column Reference with Set Analysis in QlikView

Column Reference with Set Analysis

Wed, 04 Dec 2013 19:40:21 GMT

I've created a line chart to compare activity year over year. I'm having trouble creating the expression to represent the prior year's data, using set analysis to get the year prior to the selected year. I have 2 issues:

1) I don't want to aggregate any data, I'm using column references so the data is already aggregated. (I guess I could use MAX or AVG if necessary to make this work

2) Qlikview doesn't appear to want to allow me to use column references. I get the red underline indicating a syntax error under the columns I'm trying to reference and no data is returned.

Can anyone help me understand what I need to do differently to make this work?

({$<EmailYear={$(=Max(EmailYear)-1)}>} [#Unique Open Rate])

Re: Column Reference with Set Analysis

marthacano01 — Wed, 04 Dec 2013 20:44:52 GMT

Hi Ashley. Try this:

({$<EmailYear={"=Max(EmailYear)-1)"}>} [#Unique Open Rate])

Re: Column Reference with Set Analysis

Wed, 04 Dec 2013 20:48:41 GMT

That doesn't work. It shows the red underline under the whole expression indicating an error. When I put MAX or AVG in front of it, then the red underline is only under the column reference.

Re: Column Reference with Set Analysis

marthacano01 — Wed, 04 Dec 2013 21:34:06 GMT

ok, please create a variable:

vYear = max(Y)-1

sum ({$<EmailYear = {$(vYear)} >} [#Unique Open Rate]))

Re: Column Reference with Set Analysis

marthacano01 — Wed, 04 Dec 2013 21:35:44 GMT

the field "Y" is the field that you selected

Re: Column Reference with Set Analysis

MK_QSL — Wed, 04 Dec 2013 22:08:23 GMT

Try Something Like below

SUM({<EmailYear = {'$(=Max(EmailYear)-1)'}>}SALE)

Change SUM and SALE as per your required field...

Re: Column Reference with Set Analysis

marthacano01 — Wed, 04 Dec 2013 22:30:28 GMT

Ok, you can change content of the variable:

vYear = max(EmailYear)-1

sum ({$<EmailYear = {"$(vYear)"} >} SALE)