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: 
pooja_sn
Creator
Creator

Last working day of month

Hi,

I need to display a bar graph showing sum(amount) but the graph should display only the last working day of each month on graph. I'm not sure how MonthEnd() can be used, as it gives last day of each month irrespective of weekday/weekend.

7 Replies
MK_QSL
MVP
MVP

Use Calculated Dimennsion

IF(ReportDate = Date(Floor(MonthEnd(ReportDate))),ReportDate)

TIck Suppress When Value is null

Expression

sum(Amount)

pooja_sn
Creator
Creator
Author

This will not display last working day of month. I want '30-Dec-2016' to be displayed for Dec as the background data consists of working days only.

tresesco
MVP
MVP

You may create a flag in the script for working days and then use firstsortedvalue()/max()/min() accordingly. Check the idea here: Last WorkDay of Month

MK_QSL
MVP
MVP

Data:

LOAD ReportDate,

  Date(MonthEnd(ReportDate),'MMM-YYYY') as MonthYear,

     Amount

FROM

Test.xls

(biff, embedded labels, table is Sheet1$);

Left Join (Data)

Load MonthYear,Date(Max(ReportDate)) as LastWorkingDay Resident Data Group By MonthYear;

Final:

Load ReportDate, Amount, If(ReportDate = LastWorkingDay,1,0) as LastWorkDayFlag Resident Data;

Drop Table Data;

Now use below

Dimension
ReportDate

Expression

sum({<LastWorkDayFlag = {1}>}Amount)

tresesco
MVP
MVP

Or like in attached sample. Created Month, Weekday fields.

Calculated dimension :

=Date(Aggr(Max({<WeekDay-={'Sat', 'Sun'}>}ReportDate),Month))

tamilarasu
Champion
Champion

Hi Pooja,

Have a look at the attached file.

Anonymous
Not applicable

Take a look at this post. Mays be it will help you :

Last WorkDay of Month