topic Re: Value of Date Variable Coming in Number Format in QlikView

Value of Date Variable Coming in Number Format

sunitha_chellaiah — Wed, 14 Jul 2021 08:18:21 GMT

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.

Thanks in advance.

Re: Value of Date Variable Coming in Number Format

PrashantSangle — Wed, 14 Jul 2021 08:27:26 GMT

use date() on top of it.

date(max(yourdatefield))

Regards,

Re: Value of Date Variable Coming in Number Format

sunitha_chellaiah — Wed, 14 Jul 2021 09:13:18 GMT

Hi,

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

I am using expression as shown below

Re: Value of Date Variable Coming in Number Format

PrashantSangle — Wed, 14 Jul 2021 11:14:08 GMT

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 "=$"

Re: Value of Date Variable Coming in Number Format

sunitha_chellaiah — Wed, 14 Jul 2021 11:26:52 GMT

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.

Re: Value of Date Variable Coming in Number Format

Kushal_Chawda — Wed, 14 Jul 2021 14:25:03 GMT

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

Re: Value of Date Variable Coming in Number Format

hic — Thu, 15 Jul 2021 07:22:52 GMT

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

Re: Value of Date Variable Coming in Number Format

PrashantSangle — Thu, 15 Jul 2021 04:35:32 GMT

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

Re: Value of Date Variable Coming in Number Format

tresB — Thu, 15 Jul 2021 05:30:42 GMT

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.

Re: Value of Date Variable Coming in Number Format

hic — Thu, 15 Jul 2021 06:54:07 GMT

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.

Re: Value of Date Variable Coming in Number Format

tresB — Thu, 15 Jul 2021 07:21:47 GMT

Thank you very much HIC for the clarification.

I also find that in Qlik Sense, 'Auto' format takes the Number format while it is still a dual value.

Re: Value of Date Variable Coming in Number Format

sunitha_chellaiah — Mon, 26 Jul 2021 10:59:18 GMT

Hi @hic ,

I got the correct value now by using this expression.

='$(=Max(DOJ))'

Thank you so much.