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: 
Not applicable

Convert datetime to period

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

1 Solution

Accepted Solutions
PrashantSangle

Hi,

You can format your date as you required.

Try this,

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

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

View solution in original post

6 Replies
PrashantSangle

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,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
srchilukoori
Specialist
Specialist

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

Not applicable
Author

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

PrashantSangle

Hi,

You can format your date as you required.

Try this,

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

Regards,

Great dreamer's dreams never fulfilled, they are always transcended.
Please appreciate our Qlik community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Not applicable
Author

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.

Not applicable
Author

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