How convert date to period

[paul_ripley]

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

[lorenzoconforti]

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,

[Nicole-Smith]

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';

[Nicole-Smith]

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

You can read more about that here: https://help.qlik.com/en-US/qlikview/12.0/Subsystems/Client/Content/Scripting/NumberInterpretationVa...

[paul_ripley]

..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