Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

apoorvasd
Contributor 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
galax_allu
Valued Contributor

Re: How to calculate average over a range of data?

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

Re: How to calculate average over a range of data?

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

MVP
MVP

Re: How to calculate average over a range of data?

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
Contributor II

Re: How to calculate average over a range of data?

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
Contributor II

Re: How to calculate average over a range of data?

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
Contributor II

Re: How to calculate average over a range of data?

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_narne
Valued Contributor III

Re: How to calculate average over a range of data?

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
Contributor II

Re: How to calculate average over a range of data?

Hi Sasi,

Thank you.

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

MVP
MVP

Re: How to calculate average over a range of data?

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