Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
QFanatic
Creator
Creator

General Data Question

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

1 Solution

Accepted Solutions
saran7de
Master
Master

Can you try this?

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

 quote around vMaxMonth 

View solution in original post

4 Replies
saran7de
Master
Master

Can you try this?

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

 quote around vMaxMonth 

View solution in original post

QFanatic
Creator
Creator
Author

Hello

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

rwunderlich
Luminary Alumni
Luminary Alumni

If you want to exclude Null and blank, try 

REQ_HOUR = {"*?"} 

-Rob

QFanatic
Creator
Creator
Author

Thank you Rob