# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Action-Packed Learning Awaits! QlikWorld 2023. April 17 - 20 in Las Vegas: REGISTER NOW
cancel
Showing results for
Did you mean:
Partner - Creator

## Value of Date Variable Coming in Number Format

Dear All,

While using below mentioned expression, value is coming in number date. It should come in Date format.

=\$(=Max(DOJ))

I want to use this expression in set analysis. So I am testing this in text object. Kindly see below screenshot.

Value should come as 28-06-2021. But here it is showing -1999.

1 Solution

Accepted Solutions
Employee

The dollar expansion messes things up here...

Try this, just to see what Max() returns:
=Max(DOJ)

If it returns a date, you cannot dollar expand it, because
=\$(=Max(DOJ))
will expand to
=28-06-2021
which is
-1999

To solve this, you could try quoting it:
='\$(=Max(DOJ))'

11 Replies

use date() on top of it.

like

date(max(yourdatefield))

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Partner - Creator
Author

Hi,

I have tried using date() function. But value is coming as shown in below screenshot.

I am using expression as shown below

are you sure the value stored in DOJ is in date format not in string??

try below in text box

1: Date(max(DOJ))

2: Date(max(Date#(DOJ,'DD-MM-YYYY')))

No need to write "=\$"

Great dreamer's dreams never fulfilled, they are always transcended.
Partner - Creator
Author

I want to pass that expression in set analysis.

So, I have to use "\$".

I have tried using Date# , but I am not getting desired output.

@sunitha_chellaiah  try below in text object.

date(\$(=max(Date)))

While testing the expression in text object "\$" is not useful, we use it "\$" in set analysis because it is used to evaluate the expression inside set analysis.

If above still not working then try checking in text object without "\$" . This is also not working then make sure that your DOJ is in proper Date format.

Employee

The dollar expansion messes things up here...

Try this, just to see what Max() returns:
=Max(DOJ)

If it returns a date, you cannot dollar expand it, because
=\$(=Max(DOJ))
will expand to
=28-06-2021
which is
-1999

To solve this, you could try quoting it:
='\$(=Max(DOJ))'

after long time @hic  sir I saw your reply. I always learn something new from your responses.

Great dreamer's dreams never fulfilled, they are always transcended.
MVP

Hi @hic ,

@hic wrote:

The dollar expansion messes things up here...

Try this, just to see what Max() returns:
=Max(DOJ)

If it returns a date, you cannot dollar expand it, because
=\$(=Max(DOJ))
will expand to
=28-06-2021
which is
-1999

To solve this, you could try
='\$(=Max(DOJ))'

Let me take this opportunity of getting this clarified - Max(Date), returns a number and it could return a date as well. When does max/min return a number and when a date (i.e. formatted)?

In real cases, now I always test the result and then apply additional date() for formatting. However, if I remember correctly I got (long back) cases when date formatted output used to come directly, but not in recent years; is it related to versions?

Thanks.

Employee

It is indeed linked to version. In some versions, the formatting was lost. We treated this as a minor bug, and it should be fixed now.

The functions return duals, and sometimes the formatting can be kept, sometimes not.

• Sum() and Avg() loses the formatting. The result is a new number that doesn't exist in the source data.
• Min() and Max() should pick up the format of the original data.
Community Browser