topic Re: Exclude Weekends from Straight table in QlikView

Exclude Weekends from Straight table

jlampard40 — Sat, 16 Nov 2024 01:45:44 GMT

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

Re: Exclude Weekends from Straight table

zhadrakas — Fri, 06 Dec 2019 10:08:26 GMT

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

Re: Exclude Weekends from Straight table

Anil_Babu_Samineni — Fri, 06 Dec 2019 10:09:49 GMT

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)

Re: Exclude Weekends from Straight table

jlampard40 — Fri, 06 Dec 2019 10:38:06 GMT

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

Re: Exclude Weekends from Straight table

zhadrakas — Fri, 06 Dec 2019 10:51:30 GMT

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

Re: Exclude Weekends from Straight table

jlampard40 — Fri, 06 Dec 2019 11:03:50 GMT

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

Re: Exclude Weekends from Straight table

jonathandienst — Fri, 06 Dec 2019 11:19:30 GMT

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.

Re: Exclude Weekends from Straight table

jlampard40 — Fri, 06 Dec 2019 11:26:38 GMT

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.

Re: Exclude Weekends from Straight table

zhadrakas — Fri, 06 Dec 2019 11:31:10 GMT

put that expression there:

if(FLAG_IS_WEEKEND=1, LightGray())

Re: Exclude Weekends from Straight table

jlampard40 — Fri, 06 Dec 2019 11:52:13 GMT

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

Re: Exclude Weekends from Straight table

zhadrakas — Fri, 06 Dec 2019 11:54:02 GMT

i dont understand where you want to use that. in the above expression it wont work because you excluded the weekends cells!

is that a new object where you need to set the background colour?

Re: Exclude Weekends from Straight table

jlampard40 — Fri, 06 Dec 2019 12:02:14 GMT

Jonty - how do I go about creating this 'workday' flag? That sounds particularly useful. Thanks.

Re: Exclude Weekends from Straight table

jlampard40 — Fri, 06 Dec 2019 12:07:00 GMT

OK - I get that. Excluding the weekend counts has worked fine - I get the zero in the cell which is OK. I just want to visually show on the table that these cells refer to a weekend somehow otherwise they may wonder why there is a zero count in the cells. Rgds

Re: Exclude Weekends from Straight table

zhadrakas — Fri, 06 Dec 2019 12:11:47 GMT

if the 0 is in the cell of the calculated expression, you should go the background color expression and try it like this:

if([EXPRESSION_NAME]=0, LightGray())

Re: Exclude Weekends from Straight table

jlampard40 — Fri, 06 Dec 2019 12:21:09 GMT

That works! Thank you Zhadrakas.