topic Re: Date variable in a expression in QlikView

Date variable in a expression

massimo6919 — Tue, 25 Mar 2014 10:10:49 GMT

I have this expression with a costant limit :

SUM(if(data<= '15/01/2014' ,SALDO,0))

if i want to insert a date store in a variable genn_data i wrote

sum( {$<data<=$(genn_data)>} SALDO)

but don't return the same value. What is wrong ?

Re: Date variable in a expression

SunilChauhan — Tue, 25 Mar 2014 10:13:29 GMT

sum(${<data={"<=$(=Date(genn_data,'DD/MM/YYYY'))"}>}SALDO)

Re: Date variable in a expression

MK_QSL — Tue, 25 Mar 2014 10:14:02 GMT

SUM({$<data = {'<=$(genn_data)'}>}SALDO)

Re: Date variable in a expression

alexandros17 — Tue, 25 Mar 2014 10:14:52 GMT

sum( {$<data={'<=$(genn_data)'}>} SALDO)

but you must pay attention to data format

Re: Date variable in a expression

PrashantSangle — Tue, 25 Mar 2014 10:15:07 GMT

Hi,

Try this,

sum({$<data={"<=$(genn_data)"}>} SALDO)

Regards,

Re: Date variable in a expression

ThornOfCrowns — Tue, 25 Mar 2014 10:15:21 GMT

What do you get back?

You are probably missing some quotes around your expression i.e. "<=$(genn_data)"

Re: Date variable in a expression

massimo6919 — Tue, 25 Mar 2014 10:23:16 GMT

when i use costant value the return is -120953,3

with the alternative give me i obtain -134040,26

Re: Date variable in a expression

PrashantSangle — Tue, 25 Mar 2014 10:25:43 GMT

Hi,

Then First Convert into date Format like,

sum({$<data={"<=$(=Date(genn_data,'DD-MM-YYYY'))"}>} SALDO)

Regards,

Re: Date variable in a expression

Tue, 25 Mar 2014 10:43:18 GMT

Hi,

try like this

Sum(data={"<=$(genn_data)"}>} SALDO)

Note: Your variable giving DD/MM/YYYY format and your field data should also be in the same format

Re: Date variable in a expression

Tue, 25 Mar 2014 10:48:37 GMT

Massimo,

When doing such date comparisons, I would recommend you to store an integer into your variable with the num function: num(theDateYouWant) or date#()

The comparison will be easier (no date format to write) and certainly safer.

Fabrice

Re: Date variable in a expression

alexandros17 — Tue, 25 Mar 2014 10:49:51 GMT

Ciao Massimo,

prova così:

sum( {$<data={'<=$(=genn_data)'}>} SALDO)

se ancora non funziona prova ad inserire al posto della variabile una data nel tuo formato e verifica cosa accade.

Fammi sapere

Re: Date variable in a expression

Tue, 25 Mar 2014 10:55:17 GMT

vai massimo che ce la fai

Re: Date variable in a expression

massimo6919 — Tue, 25 Mar 2014 10:59:58 GMT

Non funziona ancora .. se metto la data fissa mi restituisce 0

Re: Date variable in a expression

Tue, 25 Mar 2014 11:06:41 GMT

dipende dalle impostazioni di date del tuo sistema operativo.

devi cercare di uniformarti a questo aspetto secondo me.

non riesci a allegare qualcosa?

Re: Date variable in a expression

massimo6919 — Tue, 25 Mar 2014 11:15:56 GMT

Allegato!

Re: Date variable in a expression

mangalsk — Tue, 25 Mar 2014 12:26:33 GMT

Hello,

Kindly find the attachment where

I have changed variable

genn_data=='15/01/'&ANNO

and expression to SUM(if(data<= genn_data ,SALDO,0))

Now its giving correct value if year 2014 selected.

Regards,

Mangal

Re: Date variable in a expression

Tue, 25 Mar 2014 13:00:29 GMT

is too static: if he doesnt want to select 2014??

Re: Re: Date variable in a expression

Tue, 25 Mar 2014 13:07:22 GMT

Create variables for both Year and Month

Year =Max(Year)

Month=Max(IF(Year=$(Year),MonthNum))

Like this you can make it dynamic

By default the variables will hold max year and maxyear's corresponding max monthnum

and if any selections made from those selections the va riables will change accordingly.

Re: Date variable in a expression

mangalsk — Tue, 25 Mar 2014 13:19:23 GMT

Hiee,

Its not static.

Select any year it will give corresponding value.

Try any year selection

Re: Date variable in a expression

Tue, 25 Mar 2014 14:14:37 GMT

I meaned that if u select that field (YEAR) then this can impact with other sheets and selections: the use of variables seems to be better in my opinion, so u can select the year having a value of the expression corresponding to what u prefer