topic Re: How to convert the format of Date in QlikView

How to convert the format of Date

kunkumnaveen — Tue, 13 Jul 2021 07:36:11 GMT

Hello All,

I got two requirements to do

1) To show dates in desc order (Which is done using below code)

Dual(

date($(varMaxDate) - Iterno()+1,'DD-MM-YY'),

if( date($(varMaxDate) - IterNo() + 1) =Date($(varMaxDate)),1,Peek("MainDate")+1))as "MainDate"

2) I need to display Max Date and Min Date,hence in KPI I have written below expressions

expression : Max(MainDate),Min(MainDate)

Instead of getting Max Date and Min Date , I am getting numbers as shown above, this is because of Daul function ,hence how I convert those numbers into date in UI

Thanks

Re: How to convert the format of Date

kunkumnaveen — Tue, 13 Jul 2021 09:02:31 GMT

Is there any way to write a expression to get Max date and Min date in DD-MM-YY format

Re: How to convert the format of Date

kunkumnaveen — Mon, 19 Jul 2021 06:10:35 GMT

Hello All,

I am still looking for the suggestions on writing a expression which can get me max and

min dates in a KPI instead of numbers

Re: How to convert the format of Date

tresB — Mon, 19 Jul 2021 06:42:07 GMT

Put formatting function date() around max/min, like:

Date(max(MainDate), 'DD-MM-YYYY')

Re: How to convert the format of Date

kunkumnaveen — Mon, 19 Jul 2021 07:46:13 GMT

Hi Tresesco,

Thanks for reply,

I tried using the same ,what I am getting is some thing strange , I suppose to get 30th july 2021 as max date but I am getting

Re: How to convert the format of Date

tresB — Mon, 19 Jul 2021 08:49:14 GMT

Your approach for date creation seems to be wrong. Date is dual field by default, i.e. - if you have a proper date, you don't need to create a dual value for this field explicitly. If you are doing so, that means your number values at the back of the date values are not real date values but some custom values that are just being used for sorting. With this approach you are complicating things unnecessarily. I

Re: How to convert the format of Date

kunkumnaveen — Mon, 19 Jul 2021 09:45:21 GMT

Yes you are right its seems same to me that I am complicating the things but the thing is I need dates to be shown in descending order in the front end ,hence I am assigning the values to dates using daul function

Re: How to convert the format of Date

tresB — Mon, 19 Jul 2021 11:20:00 GMT

I see your point. In visualization you can set your sort order, but for selection panel field sort order - it seems to be always 'ascending'. There should have been a setting for this sort order; and this is really imperative for date fields.

As a workaround, you can try putting negative weight to the date field and then use another negation for any calculation on top of it.

Dual(-MainDate, MainDate) as MainDate

and then

Date(max(-MainDate), 'DD-MM-YYYY')