topic Re: Date variable and set analysis in QlikView

Date variable and set analysis

Anonymous — Wed, 06 May 2015 12:31:40 GMT

I have a date variable associated to a calendar object. This variable should be used at the following set analysis expression (text object):

= money(sum({<[Data Receb/Pagto]= {$(#Data Base)}>} [Valor Original]))

It always returns 0 (zero), although having data to sum. The [Data Receb/Pagto] field, when used in a text object returns a "date". The "Data Base" variable, when used in a text object returns a "number". In this way, I thought that the set analysis expression should be changed, but it made no diference:

= money(sum({<[Data Receb/Pagto]= {$(=#date($(#Data Base)))}>} [Valor Original]))

= money(sum({<[Data Receb/Pagto]= {$(=#num($(#Data Base)))}>} [Valor Original]))

What is wrong?

regards,

Paulo Costa

Re: Date variable and set analysis

sunny_talwar — Wed, 06 May 2015 12:39:26 GMT

#Date Base is the name of your variable?

Try this:

=Money(Sum({<[Data Receb/Pagto]= {"$(#Data Base)"}>} [Valor Original]))

Re: Date variable and set analysis

Anonymous — Wed, 06 May 2015 12:43:03 GMT

"Data Base" is the name of my variable.

The expression that you posted made no difference.

Re: Date variable and set analysis

sunny_talwar — Wed, 06 May 2015 12:45:53 GMT

My suggestion is to have no spaces between your variable name (Something like this would be ideal vDataBase)

Try this:

=Money(Sum({<[Data Receb/Pagto]= {"$(Data Base)"}>} [Valor Original])) or

=Money(Sum({<[Data Receb/Pagto]= {"$('Data Base')"}>} [Valor Original])) (never worked with variables which have a space between them, so not sure how can they be used and that's why I suggest using vDataBase)

If you do use vDataBase then you can try this:

=Money(Sum({<[Data Receb/Pagto]= {"$(vDataBase)"}>} [Valor Original]))

Re: Date variable and set analysis

Anonymous — Wed, 06 May 2015 12:54:41 GMT

I´ve changed the variable name (elimitating blank - DataBase) and it remais the same. Zero as result.

Re: Date variable and set analysis

sunny_talwar — Wed, 06 May 2015 12:57:54 GMT

I just read that your variable returns a number. Give this a try:

=Money(Sum({<[Data Receb/Pagto] = {"$(=Date($(DataBase), 'yourDateFormat'))"}>} [Valor Original]))

Replace yourDateFormat with whatever format you have for [Data Receb/Pagto]

Re: Date variable and set analysis

Anonymous — Wed, 06 May 2015 13:04:19 GMT

It´s running fine now. THANKS!

='Total, com base na data base = ' & money(sum({<[Data Receb/Pagto]= {"$(=date($(DataBase),'DD/MM/YYYY'))"}>} [Valor Original]))