Repeated value on x-axis and how to aggregate value according to Job and week

[#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. So to get repetitive value of Jobs on x-axis, I added a column  in the table named as  LinkJob ( Jobs + StartDate + StartTime) . Is it ok to use LinkJob on x-axis. I have used RunTime on y-axis. In this bar chart I have to show two things:

1)  RunTime taken by jobs.

2) Average value of RunTime taken by Jobs in previous week for each Job.

Can somebody help me to get the expression of Average value of RunTime in previous week. I am trying below expression.

        =aggr(sum(RunTime),Jobs,Weeks) 

But it is not working.

[hic]

Use Jobs as dimension, and the following as expression:

Total RunTime last week:

Sum({$<Week={"$(WeekNo(Today()-7))"}>} RunTime)

Average RunTime last week:

Sum({$<Week={"$(Week(Today()-7))"}>} RunTime)/Count({$<Week={"$(Week(Today()-7))"}>} Jobs)

See also this blog post about averages: http://community.qlik.com/blogs/qlikviewdesignblog/2013/07/29/averages

HIC

[#3]

Hi Henric,

I tried the above expression, but it's not working for me.

In this bar chart there are repetitive value of Jobs on x-axis, So I can't use Jobs on x-axis. And instead of Today's date I can select any Date or week. Can you please give me other solution for this problem.

[hic]

Please post a sample file.

HIC

[#5]

I'm really sorry. But I am not able to give sample file. I am getting Average value of RunTime taken by Jobs in the selected week correctly using below expression:-

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

But i am trying the same expression for getting Average value of RunTime taken by Jobs in the previous week

using set analysis as given below:-

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

Can you tell me what syntax error i am facing here?

[hic]

You should probably use the Set Analysis expression both in the inner and in the outer aggregation function.

Further, "Week(Start_Date)-1" will return the wrong value around new year. It's better to use "Week(Start_Date-7)".

But I a don't understand why you use the Aggr() function. It is rarely necessary to calculate averages. See also

http://community.qlik.com/blogs/qlikviewdesignblog/2013/03/11/when-should-the-aggr-function-not-be-u...

HIC

[#7]

Hi,

I am using the below expression. But still null values are coming

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