topic Re: variables in Set analysis in QlikView

variables in Set analysis

Fri, 09 May 2014 14:38:17 GMT

Hi I am using the below expression and its not working...can anybody tell me what's wrong with that

sum({$<Status_FA={'A'},Month_No={$(var_month)}>}Revenue)-sum({$<Status_FA={'A'},Month_No={$(var_month)}>}Discount)

where var_month is the variable which is assigned the value as field Month_No

Month_No is in date format.

Please help me

Re: variables in Set analysis

sudeepkm — Fri, 09 May 2014 14:42:27 GMT

try "" around the variable name

"$(var_month)"

Re: variables in Set analysis

maxgro — Fri, 09 May 2014 21:42:12 GMT

try debugging your set analysis expression (thanks to John Witherspoon)

"
For debugging set analysis, make a chart that has your set analysis expression as the first chart expression, but do NOT label the expression, and do NOT supply a caption for the chart. The caption and column heading will then show your expression. One of them (I forget which) shows the expression just like you wrote it. One of them shows it after the dollar sign substitution has taken place. Comparing the two will very often let you know what's going wrong, or at least WHERE it is going wrong.

Re: variables in Set analysis

Mon, 12 May 2014 04:39:30 GMT

Thanks Sudeep

using " " around $(var_month) is making it as string and again it's not working

Re: variables in Set analysis

jagan — Mon, 12 May 2014 06:03:13 GMT

Hi Anuj,

I think the issue is because of formatting. What is the format of var_month and Month_No is both values are in same format? If not then convert to the same format.

If your Month_No column has 1 to 12 and var_month has one value of 1 to 12, then this works.

sum({$<Status_FA={'A'},Month_No={$(var_month)}>}Revenue)-sum({$<Status_FA={'A'},Month_No={$(var_month)}>}Discount)

Regards,

Jagan.

Re: variables in Set analysis

Mon, 12 May 2014 06:04:18 GMT

try $(=var_month)

regards,

Re: variables in Set analysis

Mon, 12 May 2014 06:43:37 GMT

Hi Jagan,

Yes both Month_No and var_month are in same format which is date.

Re: variables in Set analysis

jagan — Mon, 12 May 2014 06:44:54 GMT

HI,

Try this

sum({$<Status_FA={'A'},Month_No={'$(=var_month)'}>}Revenue)-sum({$<Status_FA={'A'},Month_No={'$(=var_month)'}>}Discount)

Regards,

Jagan.

Re: variables in Set analysis

CELAMBARASAN — Mon, 12 May 2014 07:06:32 GMT

Hi,

How you created the field Month_No i.e., using Month(DateField)?

And how you assigned value to the variable using month function or something els?

Re: variables in Set analysis

Tue, 13 May 2014 05:31:05 GMT

Month_No is just the name of the field having values as Date type.

for assigning value to the variable go to Settings->Document Properties->Variables

and there you can assign any constant value or field value.

Re: variables in Set analysis

anbu1984 — Tue, 13 May 2014 05:38:33 GMT

can you share your app?

Re: variables in Set analysis

Anonymous — Tue, 13 May 2014 05:47:02 GMT

Anuj,

can you please tell us how you created/declared var_month and Month_No

else it will be all guess work ?

anant