Aggr() with set analysis

[#1]

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.

[#2]

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

[SunilChauhan]

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)

[giacomom]

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