Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
sunitha_chellaiah
Partner - Creator
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.

Please help me to correct the expression.

sunitha_chellaiah_0-1626250619345.png

Thanks in advance.

1 Solution

Accepted Solutions
hic
Employee
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))'

View solution in original post

11 Replies
PrashantSangle

use date() on top of it.

 

like

date(max(yourdatefield))

 

Regards,

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

Hi,

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

sunitha_chellaiah_0-1626253939521.png

I am using expression as shown below

sunitha_chellaiah_1-1626253997617.png

 

PrashantSangle

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.
sunitha_chellaiah
Partner - Creator
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.

Please help me to correct it.

Kushal_Chawda

@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.

hic
Employee
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))'

PrashantSangle

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.
tresesco
MVP
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. 

hic
Employee
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.