Need to display working days sum of items and add saturday, sundays item into Mondays sum

[deeavhad]

Hi,

I have prepared this chart, I want to remove weekends from it and add weekends number into coming Monday

Have attached the sample data, please help

[anthonyj · Accepted solution]

Hi,

I've used a condition using the weekday( ) function to measure if the day of the week is a Saturday or Sunday and added 2 or 1 day to the original date respectively. If it's not a Saturday or Sunday then return the original date.

Go into your data load editor where you'll find the fields and add the below "if" condition. 

if(weekday(SubField([UKI_ES_Opened],' '),0) = 5, date(SubField([UKI_ES_Opened],' ')+2),
if(weekday(SubField([UKI_ES_Opened],' '),0) = 6, date(SubField([UKI_ES_Opened],' ')+1),
date(SubField([UKI_ES_Opened],' ')))) as [UKI Shift Date]

The full code I used looked like this:

[Data]:
LOAD
[UKI_ES_Opened],
[UKI_ES_Items],
[SLA],
date(SubField([UKI_ES_Opened],' ')) as [UKI Date],
weekday(SubField([UKI_ES_Opened],' ')) as [UKI Day],

if(weekday(SubField([UKI_ES_Opened],' '),0) = 5, date(SubField([UKI_ES_Opened],' ')+2),
if(weekday(SubField([UKI_ES_Opened],' '),0) = 6, date(SubField([UKI_ES_Opened],' ')+1),
date(SubField([UKI_ES_Opened],' ')))) as [UKI Shift Date]

FROM [lib://AttachedFiles/Sample data.xlsx]
(ooxml, embedded labels, table is Sheet1);

I hope this helps.

Thanks

Anthony

[deeavhad]

Hi, I have added if condition with following code in data load editor

date(SubField([UKI_ES_Opened],' ')) as [UKI Date],
weekday(SubField([UKI_ES_Opened],' ')) as [UKI Day],

if(weekday(SubField([UKI_ES_Opened],' '),0) = 5, date(SubField([UKI_ES_Opened],' ')+2),
if(weekday(SubField([UKI_ES_Opened],' '),0) = 6, date(SubField([UKI_ES_Opened],' ')+1),
date(SubField([UKI_ES_Opened],' ')))) as [UKI Shift Date]

But there is no change in chart(Details areas below)

Dimention:- [UKI_ES_Opened.autoCalendar.Date]

Measure- sum({$<[UKI_ES_Opened.autoCalendar.Year] = {'$(=year(today()))'},[UKI_ES_Opened.autoCalendar.Month] = {'$(=month((Today())))'},[SLA] = {'24','48','72'}>}UKI_ES_Items)+NullCount({$<[UKI_ES_Opened.autoCalendar.Year] = {'$(=year(today()))'},[UKI_ES_Opened.autoCalendar.Month] = {'$(=month(Today()))'},[SLA] = {'24','48','72'}>}UKI_ES_Items)

Color Expression- 

=If((Sum({<[SLA] = {'24','48','72'}>} [UKI_ES_Items] )+Nullcount({<[SLA] = {'24','48','72'}>} [UKI_ES_Items] )) >200
and (
above(Sum({<[UKI_ES_Opened.autoCalendar.Date]=, [SLA] = {'24','48','72'}>} [UKI_ES_Items] )+NullCount({<[UKI_ES_Opened.autoCalendar.Date]=, [SLA] = {'24','48','72'}>} [UKI_ES_Items] ),1) > 200
or
below(Sum({<[UKI_ES_Opened.autoCalendar.Date]=, [SLA] = {'24','48','72'}>} [UKI_ES_Items] )+Nullcount({<[UKI_ES_Opened.autoCalendar.Date]=, [SLA] = {'24','48','72'}>} [UKI_ES_Items] ),1) > 200
)
, 'Red',green())

.............................................Please guide

[anthonyj]

Hi,

That's a great job you've done on your expressions. 

If those dimensions and measures are what you have currently in your visualisation then you'll need to swap out everywhere that UKI_ES_Opened is being used and put in [UKI Shift Date]. You'd also need to put [UKI Shift Date] in the list of dates in your "Derive fields from fields using autoCalendar;" function call.

Let me know how it goes.

Thanks

Anthony