Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Partner
Partner

Quarter Year conversion creating dates with decimals

Hi Everyone, 

In my app we have a date field that I am needing to convert to a Quarter Year format

so for a date of 2019 - 05 - 01 - 00:00:00 

I am expecting to get a value of Q2-2019

However based on the expression I have, I am getting Q1.6666666666667 - 2019 

 

The expression I am using: 

if(isnull(DATEFIELD),null(),'Q'&Ceil(month(date(left(DATEFIELD,11),'MM)))/3&'-'&Date(left(DATEFIELD,11),'YYYY'))

 

Is there something I am doing wrong in my expression. There are some values where I am getting whole numberd for the quarter dates, but most of the values seem to be having the quarter date in decimals. 

Labels (3)
1 Solution

Accepted Solutions
Highlighted
MVP
MVP

@aswinnar  make sure that your DATEFIELD is in proper date format. Based on DATEFIELD you can set the below variables in main tab

SET DateFormat='YourDATEFIELDFormat';  
SET TimestampFormat='YourDATEFIELDFormat';

or else I will try to modify your expression like below

if(isnull(DATEFIELD),null(),'Q'&Ceil(month(date(left(DATEFIELD,11)))/3)&'-'&Date(left(DATEFIELD,11),'YYYY'))

View solution in original post

4 Replies
Highlighted
MVP
MVP

@aswinnar  try below

if(isnull(DATEFIELD),null(),'Q'&Ceil(month(floor(DATEFIELD))/3)&'-'&year(floor(DATEFIELD))

Highlighted
Partner
Partner

Hi @Kush

 

That expression doesn't seem to be working. I am getting Q- as the result

Highlighted
MVP
MVP

@aswinnar  make sure that your DATEFIELD is in proper date format. Based on DATEFIELD you can set the below variables in main tab

SET DateFormat='YourDATEFIELDFormat';  
SET TimestampFormat='YourDATEFIELDFormat';

or else I will try to modify your expression like below

if(isnull(DATEFIELD),null(),'Q'&Ceil(month(date(left(DATEFIELD,11)))/3)&'-'&Date(left(DATEFIELD,11),'YYYY'))

View solution in original post

Highlighted
Partner
Partner

Hi @Kush , 

The second expression is working for me. Thanks for the help!