topic Re: General Data Question in QlikView

General Data Question

QFanatic — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: General Data Question

Saravanan_Desingh — Sat, 30 May 2020 19:17:01 GMT

Can you try this?

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

quote around vMaxMonth

Re: General Data Question

QFanatic — Sat, 30 May 2020 19:25:30 GMT

Hello

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

Re: General Data Question

rwunderlich — Sun, 31 May 2020 17:56:21 GMT

If you want to exclude Null and blank, try

REQ_HOUR = {"*?"}

-Rob

Re: General Data Question

QFanatic — Sun, 31 May 2020 20:20:58 GMT

Thank you Rob