Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
a0693051
Contributor III
Contributor III

Date field problem

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

 

1 Solution

Accepted Solutions
jyothish8807
Master II
Master II

Hi A06,

Try this:

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

 

Best Regards,
KC

View solution in original post

11 Replies
jyothish8807
Master II
Master II

Hi Jhanaarthanan,

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

Best Regards,
KC
Shubham_Deshmukh
Specialist
Specialist

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

a0693051
Contributor III
Contributor III
Author

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

Shubham_Deshmukh
Specialist
Specialist

Hi Jhanaarthanan, have you got what you were expecting?
a0693051
Contributor III
Contributor III
Author

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

 

 

 

a0693051
Contributor III
Contributor III
Author

No, Shubham.

Shubham_Deshmukh
Specialist
Specialist

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

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

jyothish8807
Master II
Master II

Hi A06,

Try this:

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

 

Best Regards,
KC