Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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
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