Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Use Calculated Dimennsion
IF(ReportDate = Date(Floor(MonthEnd(ReportDate))),ReportDate)
TIck Suppress When Value is null
Expression
sum(Amount)
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.
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
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)
Or like in attached sample. Created Month, Weekday fields.
Calculated dimension :
=Date(Aggr(Max({<WeekDay-={'Sat', 'Sun'}>}ReportDate),Month))
Hi Pooja,
Have a look at the attached file.
Take a look at this post. Mays be it will help you :