Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

beck110979
Valued Contributor III

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
Honored Contributor III

Re: last day and not last day

Hi,

maybe

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

Regards,

Antonio

16 Replies
sureshqv
Esteemed Contributor III

Re: last day and not last day

am not sure,

but you can create that date column as  lastday  ins cript

like    

date(datecolum) as Lastday

antoniotiman
Honored Contributor III

Re: last day and not last day

Hi,

maybe

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

Regards,

Antonio

beck110979
Valued Contributor III

Re: last day and not last day

Antonio thanks a lot for your quick response and help

Beck

beck110979
Valued Contributor III

Re: last day and not last day

Chanty

thanks a lot for your feedback

Beck

beck110979
Valued Contributor III

Re: last day and not last day

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
Honored Contributor III

Re: last day and not last day

Date(AddMonths(DATUM,1))

Re: last day and not last day

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

Re: last day and not last day

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

beck110979
Valued Contributor III

Re: last day and not last day

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