topic Re: Date field problem in QlikView

Date field problem

a0693051 — Thu, 13 Dec 2018 10:41:51 GMT

Hi All,

Please help me out the below:

In my table one of the field called "RENEWAL_DATE" is Char 3 value. This field is not a full year date. It represents a day and month within a year. It will have a value of between 1 and 365.

In my dashboard I would like to show like below:

if RENEWAL_DATE =001, then need to show '01-Jan'

if RENEWAL_DATE =032, then need to show '01-Feb'

if RENEWAL_DATE =060, then need to show '01-Mar', like wise I need to show in dashboard. Is it possible?

Please advise and help me.

Thanks in Advance.

Regards

Jhanaarthanan

Re: Date field problem

jyothish8807 — Thu, 13 Dec 2018 10:52:58 GMT

Hi Jhanaarthanan,

How did you decoded that 060 means 01-Mar ? What is the logic behind this.

Re: Date field problem

Shubham_Deshmukh — Thu, 13 Dec 2018 10:57:02 GMT

Try this, if you find it helpful mark it as correct so that newbies can have a quick look,

=date(DayNumberOfYear(001)+1,'DD-MMM')

Re: Date field problem

a0693051 — Thu, 13 Dec 2018 11:03:49 GMT

Hi Jyothish,

001 means 1st January, 031 is 31st Jan and like wise 060 is the 1st Mar. in Jan month 31 days and Feb month 28 days, so the value between 1 and 365 in the field.

Thanks & Regards

Jhanaarthanan

Re: Date field problem

Shubham_Deshmukh — Thu, 13 Dec 2018 11:13:23 GMT

Hi Jhanaarthanan, have you got what you were expecting?

Re: Date field problem

a0693051 — Thu, 13 Dec 2018 11:24:24 GMT

Hi Shubham,

Thanks for your reply. I have tried your logic, but 001 and 002 both shows the 01-JAN and 003 shows 02-JAN, 033 shows 01-FEB (032 should show 01-FEB) and subsequent values showing like wise.

Regards

Jhanaarthanan

Re: Date field problem

a0693051 — Thu, 13 Dec 2018 11:26:18 GMT

No, Shubham.

Re: Date field problem

Shubham_Deshmukh — Thu, 13 Dec 2018 11:34:19 GMT

put condition for 001 that when '001' is coming in field then add +1 other wise +2

Re: Date field problem

a0693051 — Thu, 13 Dec 2018 11:45:49 GMT

I put +2, for 061 it shows 03-MAR but it should be 02-MAR then remaining all values are wrongly displayed.

Re: Date field problem

jyothish8807 — Thu, 13 Dec 2018 12:06:04 GMT

Hi A06,

Try this:

=Date(YearStart(Today())+RENEWAL_DATE -1,'DD-MMM')

Re: Date field problem

a0693051 — Thu, 13 Dec 2018 13:35:19 GMT

Thanks Jyothish, it is working! Thanks a lot!!!

Re: Date field problem

jyothish8807 — Fri, 14 Dec 2018 06:10:25 GMT

You are welcome 🙂