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

How to flag last day of the month

Dear sirs, I have read how to use monthend() function in the forum, and applied the same in my qvw, but i am getting a zero value in place of 1, kindly let me know where i am mistaking.

I have created a simple Excel sheet as shown below, and expecting last day of month's value i.e. 30000000. i am using following statement in my script

IF(Date(Date,'DD/MM/YYYY') = Date(Floor(MonthEnd(Date)),'DD/MM/YYYY'),1,0)AS Monthend_flag,

DateINV_VALUE
01/01/201450000000
02/01/201420000000
03/01/201450000000
04/01/201420000000
05/01/201460000000
06/01/201490000000
07/01/201460000000
08/01/201490000000
09/01/201440000000
10/01/201430000000
11/01/201430000000
12/01/201430000000
13/01/201430000000
14/01/201430000000
15/01/201430000000
16/01/201430000000
17/01/201430000000
18/01/201430000000
19/01/201430000000
20/01/201430000000
21/01/201430000000
22/01/201430000000
23/01/201430000000
24/01/201430000000
25/01/201430000000
26/01/201430000000
27/01/201430000000
28/01/201430000000
29/01/201430000000
30/01/201430000000
31/01/201430000000
7 Replies
MarcoWedel

Hi,

Try


IF(Date#(Date,'DD/MM/YYYY') = Date(Floor(MonthEnd(Date#(Date,'DD/MM/YYYY')))),1,0)AS Monthend_flag,


Instead


Regards


Marco

MarcoWedel

formating problems on my mobile

maxgro
MVP
MVP

IF(floor(Date(Date,'DD/MM/YYYY')) = Floor(MonthEnd(date(Date,'DD/MM/YYYY'))),1,0) AS Monthend_flag;

SunilChauhan
Champion
Champion

see the attached file

code for you refrence

Tab1:

Load * inline [

Date, INV_VALUE

01/01/2014, 50000000

02/01/2014, 20000000

03/01/2014, 50000000

04/01/2014, 20000000

05/01/2014, 60000000

06/01/2014, 90000000

07/01/2014, 60000000

08/01/2014, 90000000

09/01/2014, 40000000

10/01/2014, 30000000

11/01/2014, 30000000

12/01/2014, 30000000

13/01/2014, 30000000

14/01/2014, 30000000

15/01/2014, 30000000

16/01/2014,30000000

17/01/2014, 30000000

18/01/2014, 30000000

19/01/2014, 30000000

20/01/2014, 30000000

21/01/2014, 30000000

22/01/2014, 30000000

23/01/2014, 30000000

24/01/2014, 30000000

25/01/2014, 30000000

26/01/2014, 30000000

27/01/2014, 30000000

28/01/2014, 30000000

29/01/2014, 30000000

30/01/2014, 30000000

31/01/2014, 30000000

];

Load *,

if(trim(Date1)=Trim(Date(MonthEnd(Date1),'DD/MM/YYYY')),1,0) as monthendFlag;

Load *,Date#(Date,'DD/MM/YYYY') as Date1 resident Tab1;

drop table Tab1;

hope this helps

Sunil Chauhan
its_anandrjs

Hi,

Load some thing like

Temp:

load Date#(Date,'DD/MM/YYYY') as Date;

LOAD * Inline

[

Date, INV_VALUE

01/01/2014, 50000000

02/01/2014, 20000000

03/01/2014, 50000000

04/01/2014, 20000000

05/01/2014, 60000000

06/01/2014, 90000000

07/01/2014, 60000000

08/01/2014, 90000000

09/01/2014, 40000000

10/01/2014, 30000000

11/01/2014, 30000000

12/01/2014, 30000000

13/01/2014, 30000000

14/01/2014, 30000000

15/01/2014, 30000000

16/01/2014, 30000000

17/01/2014, 30000000

18/01/2014, 30000000

19/01/2014, 30000000

20/01/2014, 30000000

21/01/2014, 30000000

22/01/2014, 30000000

23/01/2014, 30000000

24/01/2014, 30000000

25/01/2014, 30000000

26/01/2014, 30000000

27/01/2014, 30000000

28/01/2014, 30000000

29/01/2014, 30000000

30/01/2014, 30000000

31/01/2014, 30000000

];

LOAD

*,

IF(Date(Date,'DD/MM/YYYY') = Date(Floor(MonthEnd(Date)),'DD/MM/YYYY'),1,0)AS Monthend_flag

Resident Temp;

DROP Table Temp;

Hope this helps

Thanks & Regards

MarcoWedel

provided you set your date format correctly

SET DateFormat='DD/MM/YYYY';

you can simplifiy the expression to

IF(Date = Floor(MonthEnd(Date)),1,0) AS Monthend_flag;

QlikCommunity_Thread_111597_Pic1.JPG.jpg

SET DateFormat='DD/MM/YYYY';

LOAD

  *,

// IF(Date(Date,'DD/MM/YYYY') = Date(Floor(MonthEnd(Date)),'DD/MM/YYYY'),1,0)AS Monthend_flag;          // original post

// IF(Date#(Date,'DD/MM/YYYY') = Date(Floor(MonthEnd(Date#(Date,'DD/MM/YYYY')))),1,0)AS Monthend_flag;  // solution http://community.qlik.com/message/493411#493411

  IF(Date = Floor(MonthEnd(Date)),1,0) AS Monthend_flag;                                                // solution http://community.qlik.com/message/493489#493489

LOAD Date,

     INV_VALUE

FROM

[http://community.qlik.com/thread/111597]

(html, codepage is 1252, embedded labels, table is @1);

regards

Marco

VishalWaghole
Specialist II
Specialist II

Hello Abhay,

Try with this expression,

if(Date(DateField,'DD/MM/YYYY') = Date(Floor(MonthEnd(Date(DateField,'DD/MM/YYYY')))),1,0) as monthend_flag

and also find attachment which will help you.

- Regards,

Vishal Waghole