Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Not applicable

Aggr() with set analysis

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.

12 Replies
Highlighted
Not applicable

Re: Aggr() with set analysis

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

Highlighted
Not applicable

Re: Aggr() with set analysis

hi Santosh,

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

Highlighted
Not applicable

Re: Aggr() with set analysis

=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)

Highlighted
Not applicable

Re: Aggr() with set analysis

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

Highlighted
Not applicable

Re: Aggr() with set analysis

AGGR Problem with Previous month MTD

Please refer to link ,I was having same issue .

It worked for me

Highlighted
Not applicable

Re: Aggr() with set analysis

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.

Partner
Partner

Re: Aggr() with set analysis

Hi Meenakshi,

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

Regards,

Giacomo

Highlighted
Not applicable

Re: Aggr() with set analysis

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))

Highlighted
Esteemed Contributor

Re: Aggr() with set analysis

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)