topic Re: Minutes average calculation problem in QlikView

Minutes average calculation problem

its_anandrjs — Wed, 10 Aug 2011 08:54:12 GMT

Hi All,

I want to calculate minutes Avg duration of call Ids, but problem occurs that there are same minutes value values for the different call ids so how can i sum it like for call

ID01 - 26.03

ID02 - 21.37

ID03 - 26.03

......

And so far there are so many ids that has repeted values but when i apply Distinct then it count as single value but it is repeated so many times for the IDs.

For Feb i calculate there are right now three ids

and sum is

26.03 + 21.37 + 26.03 = 99.46

Sum of Mins = 99.46

Count of Calls = 3

99.46 / 3 = 33.15

Any suggestion will appreciated

Please help for that.

Regards,

Anand

Minutes average calculation problem

SunilChauhan — Wed, 10 Aug 2011 09:00:58 GMT

try this

Avg(Distinct Minutes)

Minutes average calculation problem

kaushiknsolanki — Wed, 10 Aug 2011 09:11:54 GMT

Hi,

Let me explain what i understood from your question.

As per your requirement you want avg of minutes ( even tough they are repeated many times for same ID's)

i.e in case if you have data like

ID01 - 26.03

ID02 - 21.37

ID03 - 26.03

ID01 - 21.37

So in this case the avg you want will be (26.03+21.37+26.03+21.37)/4 = 23.7

If yes then you can achieve this using this expression.

Sum(Min) /Count(ID)

Regards,

Kaushik Solanki

Minutes average calculation problem

its_anandrjs — Wed, 10 Aug 2011 09:22:10 GMT

Hi kaushik,

Yes i do this Sum(Min) /Count(ID) but wrong result see attachment please

Also when i has first 3 IDs Data

ID01 - 26.03

ID02 - 21.37

ID03 - 26.03

Result will be this:--

26.03 + 21.37 + 26.03 = 99.46

Sum of Mins = 99.46

Count of Calls = 3

99.46 / 3 = 33.15

But problem is that for ID01 26.03 repeated 20 times and and also if i sum by Sum(DIstinct Minutes) it counts only 26.03, 21.37 how i manage the ID03 - 26.03 also.

Please provide help.

Regards,

Anand

Minutes average calculation problem

its_anandrjs — Wed, 10 Aug 2011 09:22:50 GMT

Hi ,

I want to divide it by total CallIds.

Regards,

Anand

Minutes average calculation problem

SunilChauhan — Wed, 10 Aug 2011 09:24:13 GMT

try this

sum(Distinct Minutes) /Count( all ID)

Minutes average calculation problem

its_anandrjs — Wed, 10 Aug 2011 09:51:19 GMT

Hi Kaushik,

Please provide help for that.Its urgent.

Regards,

Anand

Minutes average calculation problem

kaushiknsolanki — Wed, 10 Aug 2011 09:53:29 GMT

Hi,

I dont have Qlikview on my Laptop Now. So i cant try anything.

Still i am thinking on this.

Will post as soon as i got something.

Regards,

Kaushik Solanki

Minutes average calculation problem

its_anandrjs — Wed, 10 Aug 2011 09:54:55 GMT

Hi,

When apply All it gives bad names error.

Regards,

Anand

Minutes average calculation problem

its_anandrjs — Wed, 10 Aug 2011 09:58:10 GMT

Hi,

Please it is urgent, when you got solution please provide help on thread as well as on Sample.

Regards,

Anand

Minutes average calculation problem

kaushiknsolanki — Wed, 10 Aug 2011 10:04:08 GMT

Hi,

Try Total instead of all.

Regards,

Kaushik Solanki

Minutes average calculation problem

its_anandrjs — Wed, 10 Aug 2011 13:10:25 GMT

Hi,

I tried Total but not proper result.

Regards,

Anand

Minutes average calculation problem

jedgson — Wed, 10 Aug 2011 14:20:11 GMT

Anand,

COuld you confirm that what you are tryig to achieve is an average of minutes for each distinct combination of [Call ID] & [Minutes]?

I think the easiest way to do this is create a new table in the script.

DistinctCominations:

Load

Date as DC_Date,

[Call ID] as DC_CallID,

Minutes,

Sum(1) as DC_Temp

Resident Directory

Group By Date, [Call ID];

You can then use this in a chart with an expression like Count(DC_CallID)/Sum(Minutes)

Jay

Minutes average calculation problem

its_anandrjs — Wed, 10 Aug 2011 14:23:46 GMT

Hi Jay,

Yes i want to calculate the average of minutes of callid.

Regards,

Anand

Minutes average calculation problem

jedgson — Wed, 10 Aug 2011 15:33:44 GMT

Anand,

Did my sugestion give you the desired result?

Jay

Re: Minutes average calculation problem

swuehl — Wed, 10 Aug 2011 15:58:41 GMT

Hi all,

its.anandrjs schrieb:
Hi kaushik,
Yes i do this Sum(Min) /Count(ID) but wrong result see attachment please
Also when i has first 3 IDs Data
ID01 - 26.03
ID02 - 21.37
ID03 - 26.03
Result will be this:--
26.03 + 21.37 + 26.03 = 99.46
I get a different sum = 73.43
Sum of Mins = 99.46 73.43
Count of Calls = 3
99.46 / 3 = 33.15 24.48
But problem is that for ID01 26.03 repeated 20 times and and also if i sum by Sum(DIstinct Minutes) it counts only 26.03, 21.37 how i manage the ID03 - 26.03 also.

try

=Sum(aggr(DISTINCT Minutes,[Call ID]))/count(distinct [Call ID])

Minutes average calculation problem

its_anandrjs — Wed, 10 Aug 2011 18:07:45 GMT

Hi,

No it still gives error can please do changes in my sample file.

Regards,

Anand

Re: Minutes average calculation problem

swuehl — Wed, 10 Aug 2011 18:52:22 GMT

Please find my solution attached

Regards,

Stefan

Re: Minutes average calculation problem

narband2778 — Thu, 14 Jul 2016 10:35:37 GMT

Hi Anand,

Did you find any solution.

If yes can you post it that would be much appreciated.

Thanks,

Naresh Bandari

Re: Minutes average calculation problem

effinty2112 — Thu, 14 Jul 2016 10:55:10 GMT

Hi Anand,

=Sum(Minutes) / Count(DISTINCT Ques)/Count(DISTINCT [Call ID])

gives 24.477777777778