Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Excludes weekends using an expression

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)

8 Replies
swuehl
MVP
MVP

How do the WeekDay field values look like? How were they created?

Could you post some sample data?

Anonymous
Not applicable
Author

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.

AccessTime format.PNG

swuehl
MVP
MVP

Is WeekDay a field in your data model?

If not, add it to your table LOAD:

LOAD

     Weekday(AccessTime) as WeekDay,

     AccessTime,

     ...

Anonymous
Not applicable
Author

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;

mdmukramali
Specialist III
Specialist III

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.

dinuwanbr
Creator III
Creator III

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

swuehl
MVP
MVP

Do you get an error message or just no data?

What is your default date format?

Have a look at

Dates in Set Analysis

Maybe try

AccessTime = {">=$(=Num(Makedate(2017,5,1)))"}

Anonymous
Not applicable
Author

Hi all, i used the generated field [AccessTime.autoCalendar.Date] which fixed the issue. Thanks for the useful tips provided for my future reference.