Like EOMONTH in Qliksense

[#1]

Hi All,

I'm facing issue with last week in the month.

Excel file given to me, within that, they are calculating headcount for the last week of the month (to calculate last week, they are using EOMONTH in excel).

EOMONTH for April-2016   =   18th April to 24th April.

But when I'm using weekend in qlik sense, it is showing from 25th April to 1st May (which is wrong).

can any one please help me out.

[swuehl]

Could you describe how you used EOMONTH to create the week range?

I think EOMONTH and DayName(MonthEnd()) should do the same, returning the last date of a month,given a date.

[swuehl]

But you can try

=WeekStart(  MonthEnd(DateField)+1,-1)

=WeekEnd(  MonthEnd(DateField)+1,-1)

for the last week of the month that DateField lies in and that is completely within that month.

[ThornOfCrowns]

The last week in April is 25th April - 1st May. Are you looking for the last complete week in April, which is 18th - 24th April?

[#5]

Hi Stefan,

Thanks for your quick response.

As shown in the above image last week means from 28th March to 3rd April.

But I want 21st March to 27th March

Can you please help me to get it done.

[#6]

Hi James,

Yes, I want to get 18th April to 24th April.

Can you please help me.

[swuehl]

Have you tried what I've suggested in my last answer?

=WeekStart(  MonthEnd(DateField)+1,-1)

=WeekEnd(  MonthEnd(DateField)+1,-1)

[Clever_Anjos]

Maybe this?

=If( Month(WeekEnd(MonthEnd(vDate))) <> Month(vDate),

WeekStart(MonthEnd(vDate),-1) & ' - ' & WeekEnd(MonthEnd(vDate),-1)

,WeekStart(MonthEnd(vDate)) & ' - ' & WeekEnd(MonthEnd(vDate))

)

[#9]

It's not working Stefan.

[swuehl · Accepted solution]

'It's not working' means exactely what?

No results, wrong results, application crashed?

edit:

=WeekStart(  MonthEnd(makedate(2016,3))+1,-1)       returns 21.3.2016 at my site

=WeekEnd(  MonthEnd(makedate(2016,3))+1,-1)        returns 27.3.2016 at my site