topic Re: Variables/SA in QlikView

Variables/SA

Thu, 29 Nov 2012 16:53:36 GMT

Hi,

I'm trying to get a count for the current and previous month using set analysis with a variable:

To get the current month-year:

date(today(),'MMM-YY')

Set analysis formula:

count({$<MonthYear = {$(vcurrmonth)}>}dc_code)

As far as I am aware the MonthYear and current month variable are in the same format.

Can anyone tell me what I am doing wrong?

Thanks.

Re: Variables/SA

swuehl — Thu, 29 Nov 2012 16:57:26 GMT

Try enclosing your variable in single quotes:

count({$<MonthYear = {'$(vcurrmonth)'}>}dc_code)

Re: Variables/SA

Thu, 29 Nov 2012 17:00:13 GMT

Thanks for the response.

No luck I'm afraid...although I now get a 0 instead of a null...

Re: Variables/SA

MayilVahanan — Thu, 29 Nov 2012 17:09:34 GMT

Did u check the format? Its MMM-YY or MMM-YYYY?

MonthYear format?

After that

count({$<MonthYear = {'$(vcurrmonth)'}>}dc_code)

Hope it helps

Re: Variables/SA

Thu, 29 Nov 2012 17:14:55 GMT

Yes I'm sure the format is the same as when I use an IF statement to put a flag on records for the current and previous months that works ok.

Thanks for the reply

Re: Variables/SA

swuehl — Thu, 29 Nov 2012 17:19:38 GMT

Are you trying to use the set expression in a chart with a date dimension (e.g. YearMonth)? For a comparison of counts for different time periods?

Then try

count(TOTAL {$<MonthYear = {'$(vcurrmonth)'}>}dc_code)

Re: Variables/SA

Fri, 30 Nov 2012 10:29:06 GMT

Currently I'm not using a dimension - however I tried your suggestion but it still doesn't work.

The variable is an expression, will that make a difference?

Re: Variables/SA

hic — Fri, 30 Nov 2012 11:29:59 GMT

The format is in principle irrelevant. The comparison is made on the number behind. (It's a dual so the field has both a numeric and a string representation.)

I would define the variable as

Let vCurrMonth = Num(MonthStart(today()));

and use this integer in a comparison with MonthYear (that also should be created using MonthStart).

HIC

Re: Variables/SA

kumarnatarajan — Fri, 30 Nov 2012 11:58:27 GMT

Check both field format then Try the below syntex

=count({$<MonthYear = {$(=vcurrmonth)}>}dc_code)

Re: Variables/SA

Fri, 30 Nov 2012 12:13:39 GMT

Thanks Henric

The set analysis doesn't seem to accept using num inside the expression (for the monthyear), is this something that needs to be done in the script?

Re: Variables/SA

Mon, 03 Dec 2012 15:54:49 GMT

I resolved this - by removing the calendar dates from the set analysis which were skewing the figures.