topic Re: Average Per Day in QlikView

Average Per Day

Wed, 23 Jan 2013 13:13:46 GMT

Hi Guys,

I need an expression to populate a field in a Straight Table.

The Dashboard is for reporting call volumes in a call centre. I inport the following fields:

SpecialistID (the first name of the person who took the call)

LoggedDate (the date the call came in)

I currently have a master calendar setup with the following fields:

Day

Month

Year

Quarter

I need to populate a table with the Average number of calls per day based on the current selection in the master calendar.

eg:

SpecialistID Avg calls per day

JOHN 10

JACK 12

This must be reflective of the current date range selected (so if January is selected, it will calculate average number of calls for each Specialist in January.

Any help would be greatly appreciated.

Regards

Re: Average Per Day

IAMDV — Wed, 23 Jan 2013 14:06:43 GMT

Hi L,

Have you tried using Avg(SpecialistID) as expression and SpecialistID as Dimension on a straight table. If you the LoggedDate is linked to the Calendar Table then you will see the results based on the user selections i.e. Day, Month, Quarter & Year. QlikView selections will automatically render the calculation and you don't have to do anything unlike traditional BI Systems.

Does this makes sense?

Cheers,

www.QlikShare.com

Re: Average Per Day

Wed, 23 Jan 2013 14:25:01 GMT

Hi Deepak,

This is what I originally thought but this would not calculate Average Calls Per Day.

I need it to count the number of times the Secialist's name shows up between the date ranges, then devide that by the number of days between the selection to get the Average per day.

Regards

Re: Average Per Day

IAMDV — Wed, 23 Jan 2013 14:27:30 GMT

NP. Do you mind posting some sample data? I can create a demo app and it's easier to work with your sample data. Possible?

Thanks,

www.QlikShare.com

Re: Average Per Day

jagan — Wed, 23 Jan 2013 15:18:10 GMT

Hi,

Try like this

Use SpecialistID as dimension and use the following expression

=Count(SpecialistID) / Count(Distinct LoggedDate)

Hope this helps you.

Regards,

Jagan.

Re: Average Per Day

Wed, 23 Jan 2013 16:01:01 GMT

Thanks Jagan,

This looks like it is working perfectly,

I forgot about using DISTINCT

Many thanks!

Also thanks to Deepak for starting the ball rolling!