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: 
beck_bakytbek
Master
Master

last day and not last day

Hi Folks,

i got a situation where i have my Datafield for instance:

14.06.2017

16.06.2017

30.06.2017

how can i determine the last day in each month, i want to achieve this results:

14.06.2017 - not last day

16.06.2017 - not last day

30.06.2017 - last day

Does have anybody any idea?

Thanks a lot

for your feedback

Beck

1 Solution

Accepted Solutions
antoniotiman
Master III
Master III

Hi,

maybe

IF(Date(DateField)=Floor(MonthEnd(DateField)),'Last','No Last')

Regards,

Antonio

View solution in original post

16 Replies
Chanty4u
MVP
MVP

am not sure,

but you can create that date column as  lastday  ins cript

like    

date(datecolum) as Lastday

antoniotiman
Master III
Master III

Hi,

maybe

IF(Date(DateField)=Floor(MonthEnd(DateField)),'Last','No Last')

Regards,

Antonio

beck_bakytbek
Master
Master
Author

Antonio thanks a lot for your quick response and help

Beck

beck_bakytbek
Master
Master
Author

Chanty

thanks a lot for your feedback

Beck

beck_bakytbek
Master
Master
Author

Antonio,

i have a question, if i find the last day in month, i want to add one month, for instance

30.06.2017 + 1  = 30.07.2017

i created this expression, but it does not work

if(Date(DATUM) = Floor(MonthEnd(DATUM)),AddMonths(DATUM,1),'NOT') as test

am i missing somthing?

Thanks a lot

Beck

antoniotiman
Master III
Master III

Date(AddMonths(DATUM,1))

its_anandrjs

For this better you can create one single Flag field in script part and then use it here on front end,see the script for that.

Ex:-

LET vMinDate = Num(YearStart( Today() ));
LET vMaxDate = Num(YearEnd( Today() ));

Calendar:
LOAD
Date( $(vMinDate) + IterNo() - 1 )          as [Date Field],
Month(Date( $(vMinDate) + IterNo() - 1 ))   as [Month Field],
MonthEnd(Date( $(vMinDate) + IterNo() - 1 ) ) as [Month End],
Year(Date( $(vMinDate) + IterNo() - 1 ))    as [Year Field]
AutoGenerate 1
While ($(vMinDate) + IterNo() - 1) <= $(vMaxDate);

NoConcatenate
FinalCal:
LOAD
*,
If(Floor([Date Field]) = Floor([Month End]),'Last Day','Not Last Day') as DateFlag
Resident Calendar;
DROP Table Calendar;


Img7.PNG

its_anandrjs

You can try this way also use Floor on both the sides

If(Floor([Date Field]) = Floor(MonthEnd([Date Field])),'Last Day','Not Last Day') as DateFlag

beck_bakytbek
Master
Master
Author

Antonio thanks a  lot for your feedback,

in my issue: i got a case : if i have a month: feb: 28.02.2017 my function gives me: 28.03.2017 the correct result would be = 31.03.2017.

did i something wrong?

Thanks a lot

Beck