topic Re: Expression in Variable in QlikView

Expression in Variable

christophebrault — Fri, 19 Apr 2013 13:11:23 GMT

Hi,

I'm using variable to store expression, but it's not working for a specific one :

LET vSommeCA = 'sum({$<DateFacture={">=$(=Min(Date))<=$(=Max(Date))"}>}FACT_LIGNE.CAHT)';

Any idea ?

Thanks

Re: Expression in Variable

kaushiknsolanki — Fri, 19 Apr 2013 13:17:50 GMT

Hi,

Try SET instead of LET.

SET vSommeCA = 'sum({$<DateFacture={">=$(=Min(Date))<=$(=Max(Date))"}>}FACT_LIGNE.CAHT)';

Regards,

Kaushik Solanki

Re: Expression in Variable

christophebrault — Fri, 19 Apr 2013 13:30:00 GMT

Still not working ...

Re: Expression in Variable

alexandros17 — Fri, 19 Apr 2013 13:32:13 GMT

Probably it is due to date format, try to transform everything in Num(date(...)).

this convert all dates to correspondand values and comparisons are surely correct

Obviously you have to add a field in the script that transform DateFacture to num(DateFacture)

Hopeit helps

Re: Expression in Variable

kaushiknsolanki — Fri, 19 Apr 2013 13:40:26 GMT

Hi,

Kindly upload qvw file.

Regards,

Kaushik Solanki

Re: Expression in Variable

alexandros17 — Fri, 19 Apr 2013 13:44:44 GMT

Another idea to test the expression is to substitute Min(Date) and Max(Date) with values you exect

for example:

SET vSommeCA = 'sum({$<DateFacture={">=19/04/2013<=21/04/2013"}>}FACT_LIGNE.CAHT)';

in this way you camìn understand better where is the problem

Re: Expression in Variable

Anonymous — Fri, 19 Apr 2013 14:01:18 GMT

When using dates in set analysis, I always make sure they are in date format, e.g.:

sum({$<DateFacture={">=$(=date(Min(Date)))<=$(=date(Max(Date)))"}>}FACT_LIGNE.CAHT)

Re: Expression in Variable

christophebrault — Fri, 19 Apr 2013 14:06:27 GMT

Have a look to the attached file please

Re: Expression in Variable

Fri, 19 Apr 2013 14:45:23 GMT

HI ,

I dont know but have tried having "=" at the Begining like below

SET vSommeCA = "=sum({$<DateFacture={">=$(=Min(Date))<=$(=Max(Date))"}>}FACT_LIGNE.CAHT)";

//yusuf

Re: Expression in Variable

kuba_michalik — Fri, 19 Apr 2013 14:54:36 GMT

Trouble is, SET/LET will try to expand all $() expressions at assignment time. And I suppose you want to have $(=Min(Date)) in your expression, not whatever the min/max date is at the time of reload (actually, I highly suspect it would be (internal error) 😉 )

Probably easiest way to get around this would be to mask the $() expression somehow in SET, then unmask it using LET. Or go with LET and string concatenation, but that is, in my humble opinion, a pain.

Re: Expression in Variable

kaushiknsolanki — Fri, 19 Apr 2013 18:04:54 GMT

Hi,

Try this.

SET vSommeCA = '=sum({$<DateFacture={">=$(=Min(Date))<=$(=Max(Date))"}>}FACT_LIGNE.CAHT)';

Regards,

Kaushik Solanki

Re: Expression in Variable

kuba_michalik — Mon, 22 Apr 2013 06:35:27 GMT

Had a look at your script, and the problem was what I thought it would be.

Try this for assignment in script:

SET vSommeCA = 'sum({$<DateFacture={">=$^^(=Min(Date))<=$^^(=Max(Date))"}>}FACT_LIGNE.CAHT)';
LET vSommeCA = Replace('$(vSommeCA)','^^','');

Then it works (use $(vSommeCA) in any expressions, otherwise QlikView will treat the variable as text.)