Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Not applicable
‎2014-03-23 06:46 AM

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
1,252 Views
0 Likes
7 Replies
MarcoWedel
MVP
MVP
‎2014-03-23 07:16 AM

Hi,

Try


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


Instead


Regards


Marco

933 Views
0 Likes
MarcoWedel
MVP
MVP
‎2014-03-23 07:54 AM

In response to MarcoWedel

formating problems on my mobile

933 Views
0 Likes
maxgro
MVP
MVP
‎2014-03-23 10:31 AM

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

933 Views
0 Likes
SunilChauhan
Champion II
Champion II
‎2014-03-23 11:06 AM

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
tEST.qvw
933 Views
0 Likes
its_anandrjs
MVP
MVP
‎2014-03-23 11:21 AM

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

933 Views
0 Likes
MarcoWedel
MVP
MVP
‎2014-03-23 05:42 PM

In response to 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

QlikCommunity_Thread_111597.qvw
933 Views
0 Likes
VishalWaghole
Specialist II
Specialist II
‎2014-03-24 01:40 AM

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

9 KB
111597.qvw
933 Views
0 Likes