Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
For the life of me I cannot understand why the below expression does not exclude the weekends. Help!
Count({<AccessTime={">=01/05/2017"},WeekDay={'Mon','Tue','Wed','Thu','Fri'},Message={'OK'}>} ReportID&' '&ReportName)
How do the WeekDay field values look like? How were they created?
Could you post some sample data?
Hi
So I initially used the function =Weekday(AccessTime) with AccessTime denoting dates in a format YYY-MM-DD HH:MM:SS.fff. This was performed for a filter and returned the Weekdays Mon, Tue etc.
When I was composing my set expression to provide a default view upon entry, i used the syntax WeekDay={'Mon','Tue','Wed','Thu','Fri'} but alas it did not work even though there was no error in the code expression box.
Is WeekDay a field in your data model?
If not, add it to your table LOAD:
LOAD
Weekday(AccessTime) as WeekDay,
AccessTime,
...
So I did what you said but this has thrown out another error within my set expression. This now does not work AccessTime={">=01/05/2017"}. No data is shown at all.
Here is the load script
LOAD
[ID],
[Query],
[UserId],
[ReportID],
[ReportOwnerUserId],
[ReportName],
[ReportSection],
[IsSubCube],
[IsPublic],
[IsPrivateShared],
[FormattedMessage],
[Duration],
[AccessTime],
(left([Duration],2)*60)+(Mid([Duration],4,2)*60)+(RIGHT([Duration],10)) AS [Duration Seconds],
if(Left([FormattedMessage],7)='Message','Error','OK') AS [Message],
Weekday(AccessTime) as WeekDay;
SELECT ID,
Query,
UserId,
ReportID,
ReportOwnerUserId,
ReportName,
ReportSection,
IsSubCube,
IsPublic,
IsPrivateShared,
FormattedMessage,
Duration,
AccessTime
FROM "MR_Dev".Reporting.ReportMDXQueryAudit WHERE ID >= 1161473;
Hi,
It seems AccessTime field contains Date & Time ?
if Yes then Create a New Field in the script
Date(AccessTime,'DD/MM/YYYY') as AccessDate,
then Use the AccessDate in the Set Analysis like
AccessDate={">=01/05/2017"}
I hope it will fix the issue,
Thanks,
Mukram.
Hi Cam,
First try your expression without access time (Only for week day), then try the same with access time separately.
If both are working then try to concatenate both into same expression.
Rgds,
Dinu'1
Do you get an error message or just no data?
What is your default date format?
Have a look at
Maybe try
AccessTime = {">=$(=Num(Makedate(2017,5,1)))"}
Hi all, i used the generated field [AccessTime.autoCalendar.Date] which fixed the issue. Thanks for the useful tips provided for my future reference.