Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
pooja_sn
Contributor

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
MVP
MVP

Re: Last working day of month

Use Calculated Dimennsion

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

TIck Suppress When Value is null

Expression

sum(Amount)

pooja_sn
Contributor

Re: Last working day of month

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.

MVP
MVP

Re: Last working day of month

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

MVP
MVP

Re: Last working day of month

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)

MVP
MVP

Re: Last working day of month

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

Calculated dimension :

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

Re: Last working day of month

Hi Pooja,

Have a look at the attached file.

sitrakiniaina_j
Contributor II

Re: Last working day of month

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

Last WorkDay of Month