Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Customer & Partners, DEC. 9, 11 AM ET: Qlik Product & Strategy Roadmap Session: Data Analytics REGISTER 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.

sitrakiniaina_j
Creator II
Creator II

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

Last WorkDay of Month