Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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())
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
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)
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
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
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
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.
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.
put that expression there:
if(FLAG_IS_WEEKEND=1, LightGray())
Thanks Zhadrakas but that doesn't want to work..?