topic Re: Convert datetime to period in QlikView

Convert datetime to period

Tue, 25 Mar 2014 12:10:39 GMT

Hello,

Our database uses 'Periods' in the financial year quite extensively. To clarify for anyone not familiar, an example of a period would be '201401', which would mean the first month in the 2014 financial year (April 2013). Similarly, the current period is '201412', meaning March 2014.

What I'm trying to do is compare data in a table which has a standard datetime field, rather than period information, to a standard set of variables containing period information. I am trying to count the number of matters opened up until the current period in the current financial year.

The formula I'd like to use is something like =Count({<MATTER.OPEN_DATE={$(YTD)}>} MATTER.CLNT_MATT_CODE)

...where the 'YTD' variable looks like ">=$(=Num(Left($(currentPeriod), 4) & '01'))<=$(=Num($(currentPeriod) - 1))"

...and the 'currentPeriod' variable is 201412

Because the MATTER.OPEN_DATE field is currently displayed in format '23/01/2009 00:00:00', the formula as it is will currently return no data. Can anyone suggest how I can convert the date as it is into the Period format described above, so that I can consistently count the data up until the current financial period? (Or any alternatives if there's a better way of doing this).

Thanks, Gavin

Re: Convert datetime to period

PrashantSangle — Tue, 25 Mar 2014 12:31:24 GMT

Hi,

You can do one thing instead of changing in your set analysis,

You can Convert your ' MATTER.OPEN_DATE' from timestamp to Date in Your Script.

like Date(floor(MATTER.OPEN_DATE),'DD/MM/YYYY') as New_MATTER.OPEN_DATE

Regards,

Re: Convert datetime to period

srchilukoori — Tue, 25 Mar 2014 14:08:07 GMT

Creating a new Period field on the table will be an easier and much optimized way to implement your YTD logic.

Re: Convert datetime to period

Tue, 25 Mar 2014 14:45:52 GMT

Thank you max dreamer - but I think this will still leave me with a date rather than a period?

Re: Convert datetime to period

PrashantSangle — Tue, 25 Mar 2014 14:49:48 GMT

Hi,

You can format your date as you required.

Try this,

Date(floor(MATTER.OPEN_DATE),'YYYYMM') as New_MATTER.OPEN_DATE

Regards,

Re: Convert datetime to period

Tue, 25 Mar 2014 14:51:55 GMT

Thanks srchilukoori. I'd like to do this but unfortunately the database is 3rd party and any changes to the database are very strongly discouraged. I suppose I could create a view instead, but I was rather hoping there would be a way to do this in either the load script or a formula.

Re: Convert datetime to period

Tue, 25 Mar 2014 15:04:54 GMT

Thanks again max dreamer, that's looking good now.