Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
apoorvasd
Creator II
Creator II

How to calculate average over a range of data?

Hello,

I have a requirement where in i need to create a line graph which shows the average usage of licenses per day. Now the problem is, i have a "Time" field which has data for every 15 mins. This "Time" has data for entire day (i.e. from early morning 12 AM to midnight 11:59 PM). Now i need to calculate the average for only working hours say, from 8 AM to 8 PM per day.

If i calculate average for the entire day considering the Time from morning till evening, it would depict a wrong average, as the licenses will not be utilized for half of the day. So how can i pick up just that Time for a day and calculate the average license utilization over a day?

Any idea?

Thank you.

11 Replies
Anonymous
Not applicable

Hi

Following will help for determining a DAY (like from 8 AM TO 8 PM) ??

with this hope you can go with  Average ..!!?

Date help

Solution given by Andres

MarcoWedel

Hi,

you could select 8 AM to 8 PM in a list box of your time field or restrict your Avg() function to this interval using a set expression.

regards

Marco

swuehl
MVP
MVP

Maybe something like

=Avg( If(Hour(TIME) >=8 AND Hour(TIME) <=20, USAGE))

or

=Avg({<TIME = {">='08:00 AM'<='08:00 PM'"}>} USAGE)

for example in a chart with dimension DATE, to get the avg USAGE in your time slots.

(Finding the right format for your numeric search in the set analysis might be a bit difficult)

apoorvasd
Creator II
Creator II
Author

Hello Stefan,

Thank you for the suggestion.

I tried both the expressions. 1st expression isn't filtering the data between 8 AM to 8 PM. Average still shows for the entire 24 hours (which is dropping the average by 50%)

for ex: if the average utilization of license for time period 8 AM to 8 PM is 32, my graph displays 16 (because it it considering entire 24 hours).

And the 2nd expression is not working.

Any further suggestions?

Thank you.

apoorvasd
Creator II
Creator II
Author

Hi Marco,

Thank you for the suggestion.

But i don't want to select time separately from the list box. I would like to implement that filter in my graph itself.

Any other suggestion?

Thank you.

apoorvasd
Creator II
Creator II
Author

Hello,

Thank you for the suggestion.

Expression in that thread determines DAY. But my requirement is to filter the "Time" values between 8 AM to 8 PM and then take the average of licenses used within that Time frame per day.

Any other idea?

Thank you.

sasikanth
Master
Master

Try Something like,

Var Start=num('08:00:00 AM')

Var End=num('08:00:00 PM')

load *,Num(Time( Time_Field)) as Time from Table;

Exp : AVG(AGGR(Count(If(Time>Start and Time<End,LICENCENO)),Day))

May be helpful

apoorvasd
Creator II
Creator II
Author

Hi Sasi,

Thank you.

But its not working. I can't see the data for the average in my graph.

swuehl
MVP
MVP

Please describe your data model / tables a bit closer, and maybe add some sample records and your expect result.