topic Re: How convert date to period in QlikView

How convert date to period

paul_ripley — Wed, 17 Oct 2018 16:47:14 GMT

Hello

I have a group of dates that I want to convert in my load script to periods.

For example Anything from 01/06/2018 00:00:00 to 30/06/2018 00:00:00 would be classed as 201803

The reason being that the year end is 31/3 so period 1 is 1 April- 30 April

How would I convert these in the script? Also as an example 31/01/2018 would be 201710 as it is from the last year

I hope that makes sense

Kind regards

Paul

Re: How convert date to period

lorenzoconforti — Wed, 17 Oct 2018 16:58:21 GMT

Maybe something like this?

if(num(month(Date)) > 3,

year(Date) & num((num(month(Date)) - 3),'00'),

(year(Date) - 1) & num((9 + num(month(Date))),'00')

) as financialmonth,

Re: How convert date to period

Nicole-Smith — Wed, 17 Oct 2018 17:03:53 GMT

Load script like this:

Data:
LOAD Date,
Year(AddMonths(Date, -3))*100 + Month(AddMonths(Date, -3)) AS Period
INLINE [
Date
01/06/2018 00:00:00
30/06/2018 00:00:00
31/01/2018 00:00:00
];

Will return these values:

Date	Period
31/01/2018 00:00:00	201710
01/06/2018 00:00:00	201803
30/06/2018 00:00:00	201803

If your timestamps are formatted like that, you'll need to make sure this variable is set appropriately:

SET TimestampFormat='DD/MM/YYYY hh:mm:ss';

Re: How convert date to period

Nicole-Smith — Wed, 17 Oct 2018 17:20:41 GMT

Another way to do it (if it won't break other pieces of your data) is to set the FirstMonthOfYear variable to 4.

Re: How convert date to period

paul_ripley — Thu, 25 Oct 2018 10:18:03 GMT

..thanks so much for your help. I haven't had chance to put into my app yet but i'll let you now how I get on when I get a chance, thanks again Paul