topic Re: Aggr() with set analysis in QlikView

Aggr() with set analysis

Tue, 02 Dec 2014 07:00:17 GMT

hi,

I am making a bar chat which is showing RunTime(y-axis) V/S Jobs(x-axis). But there are many Jobs which are running multiple times in a day. There may be possibility that on the same date and time , many jobs are running.

I used the below expression for Average value of RunTime taken by each Job in current week which is giving right value:-

=sum(aggr(sum(RunTime),Week, Jobs))/ Sum(aggr(Count(Job_Count), Week, Jobs))

But I am getting problem with the expression of Average value of RunTime taken by each Job in previous week. I am using below expression:

=Sum(aggr(sum({$<Week = {" $(=Week(Start_Date - 7)) "}>}RunTime),Week, Jobs))/ Sum(aggr(Count({$<Week = {" $(=Week(Start_Date - 7)) "}>}Job_Count), Week, Jobs)

Can somebody help me to know what is wrong with this expression.

Re: Aggr() with set analysis

Tue, 02 Dec 2014 07:06:50 GMT

You need to remove Date selection by using aggr ({<Start_Date=, Week=>}

Re: Aggr() with set analysis

Tue, 02 Dec 2014 07:11:53 GMT

hi Santosh,

I am not getting you. Can you please explain me more.

Re: Aggr() with set analysis

Tue, 02 Dec 2014 07:19:26 GMT

=Sum(aggr({<Week=>}sum({$<Week = {" $(=Week(Start_Date - 7)) "}>}RunTime),Week, Jobs))/ Sum(aggr({<Week=>}Count({$<Week = {" $(=Week(Start_Date - 7)) "}>}Job_Count), Week, Jobs)

Re: Aggr() with set analysis

Tue, 02 Dec 2014 07:28:40 GMT

It is not working. Some syntax error is coming. Can you suggest me something else.

Re: Aggr() with set analysis

Tue, 02 Dec 2014 07:32:56 GMT

AGGR Problem with Previous month MTD

Please refer to link ,I was having same issue .

It worked for me

Re: Aggr() with set analysis

Tue, 02 Dec 2014 08:45:29 GMT

I try the below expression

=Sum({<Week=>}aggr(sum({$<Week = {" $(=Week(Start_Date - 7)) "}>}RunTime),Week, Jobs)) / Sum({<Week=>}aggr(Count({$<Week = {" $(=Week(Start_Date - 7)) "}>}Job_Count), Week, Jobs)

but I am getting null value.

Aggr() with set analysis

Re: Aggr() with set analysis

giacomom — Tue, 02 Dec 2014 09:33:10 GMT

Hi Meenakshi,

I think you forgot a ')' parenthesis at the end of the expression.

Regards,

Giacomo

Re: Aggr() with set analysis

Tue, 02 Dec 2014 09:41:44 GMT

I used the below expression after using ')' parenthesis at the end of the expression. But I am getting null value.

=Sum({<Week=>}aggr(sum({$<Week = {" $(=Week(Start_Date - 7)) "}>}RunTime),Week, Jobs)) / Sum({<Week=>}aggr(Count({$<Week = {" $(=Week(Start_Date - 7)) "}>}Job_Count), Week, Jobs))

Re: Aggr() with set analysis

SunilChauhan — Tue, 02 Dec 2014 09:47:20 GMT

use sum in place of count .beacuse job count is already a count.try below

Sum({<Week=>}aggr(sum({$<Week = {" $(=Week(Start_Date - 7)) "}>}RunTime),Week, Jobs)) / Sum({<Week=>}aggr(sum({$<Week = {" $(=Week(Start_Date - 7)) "}>}Job_Count), Week, Jobs)

Re: Aggr() with set analysis

Tue, 02 Dec 2014 09:53:07 GMT

I used Sum() Function instead of Count(). But it is not making any difference. Still null values are coming.

Re: Aggr() with set analysis

SunilChauhan — Tue, 02 Dec 2014 10:14:40 GMT

better if you could share sample file

Sum(aggr(sum({$<Week = {" $(=Week(Start_Date - 7)) "},Week=>}RunTime),Week, Jobs)) / Sum(aggr(sum({$<Week = {" $(=Week(Start_Date - 7)) "},Week=>}Job_Count), Week, Jobs)

Re: Aggr() with set analysis

giacomom — Tue, 02 Dec 2014 10:32:53 GMT

Hi Meenakshi,

I think you can use a simpler expression by exploiting the power of the sum() function as explained in the Reference Manual:

sum([{set_expression}][distinct][total[<fld {, fld}>]] expression)

Returns the aggregated sum of expression or field iterated over the chart dimension(s).

Can you try to use something like this:

= sum(RunTime) / sum( total <Jobs, Week> RunTime)

and set the fields Jobs and Week as the dimensions of the chart?

Let me know,

Giacomo