Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

camjvine18
Contributor

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
MVP
MVP

Re: Excludes weekends using an expression

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

Could you post some sample data?

camjvine18
Contributor

Re: Excludes weekends using an expression

Hi

So I initially used the function =Weekday(AccessTime) with AccessTime denoting dates in a format YYY-MM-DD HH:MMSmiley FrustratedS.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

MVP
MVP

Re: Excludes weekends using an expression

Is WeekDay a field in your data model?

If not, add it to your table LOAD:

LOAD

     Weekday(AccessTime) as WeekDay,

     AccessTime,

     ...

camjvine18
Contributor

Re: Excludes weekends using an expression

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
Valued Contributor II

Re: Excludes weekends using an expression

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
Contributor III

Re: Excludes weekends using an expression

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

MVP
MVP

Re: Excludes weekends using an expression

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)))"}

camjvine18
Contributor

Re: Excludes weekends using an expression

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