Skip to main content
Announcements
Customer Spotlight: Discover what’s possible with embedded analytics Oct. 16 at 10:00 AM ET: REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Convert a transaction date to the first day of that month

Hi everyone,

Can anyone tell me how I can convert a transactional date to always the first day of that particular month?

Some examples:

  • 25/05/2015 should now become 01/05/2015
  • 12/02/2014 should now become 01/12/2014

The real date formation that I'm currently loading in QV is YYYYMMDD (e.g. 20150528)

This is in the load script converted to DDMMYY (28052015) by means of the below but now I want the system to make a new date so that it will read 01052015

(makedate(left(Trim([Invoice Date]),4),mid(trim([Invoice Date]),5,2), right(Trim([Invoice Date]),2))) as Date

Any suggestions are welcome.


Thanks,

1 Solution

Accepted Solutions
robert_mika
Master III
Master III

Another way

=date(date#('01'&MID('20150528',5,2)&LEFT('20150528',4),'DDMMYYYY'),'DD/MM/YYYY')

View solution in original post

8 Replies
petter
Partner - Champion III
Partner - Champion III

Have a look at the MonthStart() function

aveeeeeee7en
Specialist III
Specialist III

Hi

Use this:

MonthStart(Date(YourDateField,'DD/MM/YYYY')) AS New_Date

This will Resolve your issue.

Regards

Av7eN

Not applicable
Author

Hi,

Try using MonthStart function.

MonthStart([Invoice Date]).

Regards,

Jemimah

aveeeeeee7en
Specialist III
Specialist III

Here is the Example:

LOAD *, MonthStart(Date(Date,'DD/MM/YYYY')) AS New_Date Inline [

Date

41975

42149

42329

];


Result:

Date Format.png

Hope that helps.

Regards
Av7eN

robert_mika
Master III
Master III

Another way

=date(date#('01'&MID('20150528',5,2)&LEFT('20150528',4),'DDMMYYYY'),'DD/MM/YYYY')

ankitaag
Partner - Creator III
Partner - Creator III

Hi,

Try

LOAD MonthStart(Date#(Dates,'DDMMYYY')) as Date

FROM Table

This will work..

Thanks and Regards,

Ankita

Not applicable
Author

Hi everyone,


Thanks for all your input. I tried the solution as suggested by Robert Mika which worked great.

Regards,

René

MarcoWedel

Hi,

another solution could be:

QlikCommunity_Thread_166503_Pic1.JPG

LOAD *,

    Date(MonthStart(Date),'DDMMYY') as MonthStart;

LOAD Date(Date#([Invoice Date], 'YYYYMMDD'),'DDMMYY') as Date

INLINE [

    Invoice Date

    20140109

    20140212

    20150525

    20150528

    20151231

];

hope this helps

regards

Marco