Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi guys,
I need some assistance with a data question.
I have Sales info, per Salesman, per day, per hour. Like this..
SalesMan Day WeekDay Hour Amount
Peter 20200501 Monday 00 501
Mark 20200501 Monday 12 10
I need to calculate the Average Sales, Per Weekday, per Timeslot (Per SalesMan).
Not so difficult. But...sometimes there is a problem in the underlying data Source - where the 'Hour' is either blank or Null. Obviously this affects my average because it is completely skewed. (my expression in chart looks like this...fiy..(I tried with REQ_HOUR = {'* '} to get only NON NULL Values but that is not working as expected...
avg({$<PERIOD={$(vMaxMonth)},REQ_DATE=, TYPE_GRAPH = {'HOURLY'},REQ_HOUR = {"*"} >}total <WKDAY,REQ_HOUR> HOUR_VOLUME)
Do you have any ideas on how I can address this issue? I have tried hard-coding a 'NULL' value as the Hour field, but it screws up all the averages for that Salesman for other days in my chart (with 'normal' data I'd be dividing by 24 Distinct Hours, now I'd be dividing by 25...00 to 23 AND the 'Faulty' addition unassigned value.).
Thanks very much
Can you try this?
avg({$<PERIOD={'$(vMaxMonth)'},REQ_DATE=, TYPE_GRAPH = {'HOURLY'},REQ_HOUR = {"*"} >}total <WKDAY,REQ_HOUR> HOUR_VOLUME)
quote around vMaxMonth
Can you try this?
avg({$<PERIOD={'$(vMaxMonth)'},REQ_DATE=, TYPE_GRAPH = {'HOURLY'},REQ_HOUR = {"*"} >}total <WKDAY,REQ_HOUR> HOUR_VOLUME)
quote around vMaxMonth
Hello
The Expression works as it is, but the result is wrong because of the underlying data issue i explained
If you want to exclude Null and blank, try
REQ_HOUR = {"*?"}
-Rob