Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Convert Date and group

Hello.

so - I have seen plenty of advice on converting integer fields into dates along with formatting as per below:

  • date(MonthStart(%_WO_Requested),'MMM-YY') as [Start Month]
  • date(MakeDate (1970,01,01)+ FLD_REPORTING_CLEAREDTIME /24/60/60,'MMM-YY') as Resolve_Month,

Both of these statements do the job, however, instead of leaving me with a list of 12 dates for a year (e.g. Jan-16, feb-16 etc etc), i get a massive list of dates that appear to be the same (e.g. Jan-16,Jan-16,Jan-16,Feb-16, Feb-16 etc etc).

I think this is because the underlying integer is a unique number which represents day, month, year, hours, minutes and seconds.

There are 2 date representations that are extracted from the remedy system:

42181

and

1435309668

so, my question is, once i have converted the date into MMM-YY, how do i create a calendar object that only shows the 12 months for the year, and not a date for each unique integer number?

4 Replies
marcus_sommer

You need to convert your timestamps with a rounding-function like:

date(floor(MonthStart(%_WO_Requested)),'MMM-YY') as [Start Month]

then formatting will only change the displaying but not the values itself.

- Marcus

Anonymous
Not applicable
Author

Hi. its not quite working. In the attached image, the left hand date is what I had with the expression I had in my initial post, and the right hand dates is using floor' in the expression.

i am just after one instance of the mmm-yy appearing for each month of the year, which will display all values relevant to that month?

dates.jpg

marcus_sommer

Please provide a few values from your %_WO_Requested field and how the used expression look like.

- Marcus

Anonymous
Not applicable
Author

Hi Marcus.

i have found a way to get the result i am looking for - but i appreciate your time looking into this for me.

Thanks