topic Re: Convert Date and group in QlikView

Convert Date and group

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Convert Date and group

marcus_sommer — Fri, 30 Sep 2016 16:27:08 GMT

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

Re: Convert Date and group

Anonymous — Tue, 04 Oct 2016 08:33:59 GMT

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?

Re: Convert Date and group

marcus_sommer — Tue, 04 Oct 2016 16:13:06 GMT

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

- Marcus

Re: Convert Date and group

Anonymous — Wed, 05 Oct 2016 09:33:31 GMT

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