topic Re: variable error in expression in QlikView

variable error in expression

gerhardl — Tue, 08 Jan 2013 08:37:23 GMT

Hi,

Please help me figure out what is wrong with my variable in this expression. It just returns null.

=count({<[Decision Date]={$(vYesterdayApp)}>}[Decision Result])

This is my variable:

vYesterdayApp = max([Decision Date])

When I create a text obect with just the variable value I get 2013/01/07, which is correct.

Re: variable error in expression

khadeer — Tue, 08 Jan 2013 08:39:20 GMT

Try this

=count({<[Decision Date]={"$(vYesterdayApp)"}>}[Decision Result])

Re: variable error in expression

gerhardl — Tue, 08 Jan 2013 08:41:09 GMT

Hi,

Tried that - it returns 0

Re: variable error in expression

khadeer — Tue, 08 Jan 2013 08:43:19 GMT

I hope there is no problem , what u written in set analysis,.... check ur dates, both have same formats r nt.if its r correct format

try this

By pass ur year fields n month fields

=count({<[Decision Date]={"$(vYesterdayApp)"},Year=,Month=>}[Decision Result])

Re: variable error in expression

gerhardl — Tue, 08 Jan 2013 08:46:30 GMT

Which "both" are you referring to? There is only one date used in this expression.

When I do a text object I get 2013/01/07.

When I put that in my expression I get the correct answer:

=count({<[Decision Date]={'2013/01/07'}>}[Decision Result])

Re: variable error in expression

adamwilson — Tue, 08 Jan 2013 12:06:14 GMT

try this:

=count({<[Decision Date]={ "$(=vYesterdayApp)" }>}[Decision Result])

Re: variable error in expression

gerhardl — Tue, 08 Jan 2013 12:09:05 GMT

Hi,

I did try that, also doesn't work.

The moment the variable is between quotes QV doesn't see it as a variable.

Re: variable error in expression

adamwilson — Tue, 08 Jan 2013 12:19:50 GMT

I think this might work this time:

=count({<[Decision Date]={ "= $(=vYesterdayApp)" }>}[Decision Result])

Re: variable error in expression

gerhardl — Tue, 08 Jan 2013 12:22:41 GMT

Afraid not.

That gives me the same answer as

=count({<[Decision Date]={*}>}[Decision Result])

or just

count([Decision Result])

Re: variable error in expression

jonathandienst — Tue, 08 Jan 2013 12:32:55 GMT

The set expression uses the text form of dates. Max(....) returns a number rather than a date. So this might work:

=count({<[Decision Date] = {"=$(=Date(vYesterdayApp))"}>} [Decision Result])

This assumes Decision Date is in the default date format (in Set DateFormat = ....).

Regards

Jonathan

Re: variable error in expression

er_mohit — Tue, 08 Jan 2013 12:33:40 GMT

try this

count({$<[Decision Date]={$(#=only(vYesterdayApp))}>}[Decision Result])

hope it helps

Re: variable error in expression

jonathandienst — Tue, 08 Jan 2013 12:35:35 GMT

You could ditch the vYesterdayApp variable with:

=count({<[Decision Date] = {"=$(=Date(Max([Decision Date])))"}>} [Decision Result])

Regards

Jonathan

Re: variable error in expression

adamwilson — Tue, 08 Jan 2013 12:36:59 GMT

your variable is the issue

vYesterdayApp = max([Decision Date])

when evaluating the expression it is getting the max for each row,

Re: variable error in expression

christian77 — Tue, 08 Jan 2013 13:28:11 GMT

Hi:

Have you tried groupping?

vYesterdayApp = aggr( max([Decision Date]) , [Decision Result])

It should give you the max for each [Decision Result].

Re: variable error in expression

gerhardl — Tue, 08 Jan 2013 13:32:25 GMT

Tried all 3 above suggestions with no luck.

I like the idea of not using the variable, and rather using max() in my set analysis - but please help me with the syntax.

=count({<[Decision Date] = {"=$(=Date(Max([Decision Date])))"}>} [Decision Result])

returns a count of all apps.

Re: variable error in expression

gerhardl — Tue, 08 Jan 2013 13:33:34 GMT

Hi,

Do you mean I should change my Variable?

The variable already gives me the correct answer (Yesterday's date). The problem is that the variable does not work in my expression.

Re: variable error in expression

christian77 — Tue, 08 Jan 2013 13:40:06 GMT

Then, create the variable in the variable panel and use it between simple quotes in your set, like

Field = {'$(Variable)'}

that always works.

Re: variable error in expression

gerhardl — Tue, 08 Jan 2013 13:42:24 GMT

If by Variable Panel you mean in Variable Overview, then, as mentioned above - this is not working.

I use variables like this often - something here is just wrong. Guessing because of the date format

Re: variable error in expression

gerhardl — Tue, 08 Jan 2013 13:45:50 GMT

Okay I gave up and did something different.

In my load script I created a new field which counts the number of days since [Decision Date], from today.

Then I use this in my Expression (i.e. where Days Since Decision is 1).

Re: variable error in expression

adamwilson — Tue, 08 Jan 2013 13:46:52 GMT

In your load script:

Temp:

LOAD

date(Max([Decision Date])) as YesterdayApp

RESIDENT TableName;

LET vYesterdayApp = date(peek('YesterdayApp',-1,'Temp'));

Drop Table Temp;

expression:

=count({<[Decision Date]={ "$(=vYesterdayApp)" }>}[Decision Result])