Exclude Weekends from Straight table

[jlampard40]

Hi guys - how can I produce a straight table, based on the following set analysis.  I want to show the total count of our demand, based on request date, over the past 5 days (in one column), past 4 days (in one column), past 3 days(in one column), past 2 days (in one column) and yesterday (in one column).  I've sorted the individual columns as per following set analysis per column expression:

=Count({<Activity_or_Demand={'Demand'},[Request_Date] = {"$(=Date(Today()-5))"}>}Event_Key)

=Count({<Activity_or_Demand={'Demand'},[Request_Date] = {"$(=Date(Today()-4))"}>}Event_Key)

=Count({<Activity_or_Demand={'Demand'},[Request_Date] = {"$(=Date(Today()-3))"}>}Event_Key)

=Count({<Activity_or_Demand={'Demand'},[Request_Date] = {"$(=Date(Today()-2))"}>}Event_Key)

=Count({<Activity_or_Demand={'Demand'},[Request_Date] = {"$(=Date(Today()-1))"}>}Event_Key)

The problem I'm having is that the weekends are creeping into this based on the above set analysis and I would like to somehow exclude these from the straight table all together.  I only have 'Request Date' as the field referring to date and somehow need Qlikview to work out which is a Saturday or Sunday (on Request Date only) and exclude from the straight table.

If you think a straight table is the wrong way to approach this, I'd appreciate your advice guys.  Rgds

[zhadrakas]

easiest way is to add this to your expression:

num(weekday([Request_Date])) < {"5"}

best way would be to add a flag on script side called FLAG_IS_WEEKEND where monday-friday have 0, saturday and sunday have 1 . then you could do it like this:

FLAG_IS_WEEKEND = {"0"}

regards

tim

[Anil_Babu_Samineni]

I can give one, remain manage your self

WeekDay(Request_Date) as WeekDay // Create this as field from script

And use expression as

=Count({<Activity_or_Demand={'Demand'}>*<[Request_Date] = {"$(=Date(Today()-5))"}>*<Request_Date-={'Sat', 'Sun'}>}Event_Key)

[jlampard40]

Can you show me how to include the 'FLAG_IS_WEEKEND' flag into script side?  Also, how do I incorporate the Num element into my set analysis?  Thanks

[zhadrakas]

add to your script:

if(num(weekday([Request_Date]))<5, 0,1) as FLAG_IS_WEEKEND

then you can use:

=Count({<Activity_or_Demand={'Demand'},[Request_Date] = {"$(=Date(Today()-5))"}, FLAG_IS_WEEKEND = {"0"}>}Event_Key)

sorry my first approach will not work in set analysis.

num(weekday([Request_Date])) < {"5"}

 regards

tim

[jlampard40]

That works well -I get a '0' in the table where the weekend lies.  Next step - how can I write set analysis to now calculate the average per day, over the past 8 days EXCLUDING weekends.  Really appreciate your help.  Kind regards

[jonathandienst]

Perhaps something like

=Count({<
    Activity_or_Demand={'Demand'},
    [Request_Date] = {"$(=Date(Today()-5))"}
           * P({<Request_Date = {"=Weekday(Request_Date)<=4"}>} Request_Date)
>}Event_Key)

Although a better approach would be to create a work day flag associated with the Request_Date in your load script and use this flag in a simpler set expression.

[jlampard40]

Thanks.  One last question.  In the columns which are identified as weekends, using the 'FLAG_AS_WEEKEND' - how can I change the background colour of these weekend cells to light grey so as to visually differentiate them in the table?  Thanks.

[zhadrakas]

put that expression there:

if(FLAG_IS_WEEKEND=1, LightGray())

[jlampard40]

Thanks Zhadrakas but that doesn't want to work..?