General Data Question

[QFanatic]

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

[Saravanan_Desingh · Accepted solution]

Can you try this?

avg({$<PERIOD={'$(vMaxMonth)'},REQ_DATE=, TYPE_GRAPH = {'HOURLY'},REQ_HOUR = {"*"} >}total <WKDAY,REQ_HOUR> HOUR_VOLUME)

 quote around vMaxMonth 

[QFanatic]

Hello

The Expression works as it is, but the result is wrong because of the underlying data issue i explained

[rwunderlich]

If you want to exclude Null and blank, try 

REQ_HOUR = {"*?"} 

-Rob

[QFanatic]

Thank you Rob