New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save \$400. Learn More
Contributor II

Average Age Calculation in Text Box

I am trying to calculate the average age of 'open' and "on-hold" records in a data set (see attached spreadsheet) and display in a text box.

The expression I am using is this:

=AVG(Aggr(sum({<Iss_Status = {'Open', 'On-Hold'}>}Iss_Age),Iss_Age))

The result I am getting s 239.83333 and it should be 195 (per the spreadsheet).

What am I doing wrong in the formula?

Thanks.

Jeff

Tags (1)
1 Solution

Accepted Solutions MVP

Re: Average Age Calculation in Text Box

Try

=AVG({<Iss_Status = {'Open', 'On-Hold'}>} Aggr(sum({<Iss_Status = {'Open', 'On-Hold'}>}Iss_Age),Iss_Age))

edit:

No, this should be correct:

=AVG(Aggr(sum({<Iss_Status = {'Open', 'On-Hold'}>}Iss_Age),Issue_ID))

7 Replies MVP

Re: Average Age Calculation in Text Box

Try

=AVG({<Iss_Status = {'Open', 'On-Hold'}>} Aggr(sum({<Iss_Status = {'Open', 'On-Hold'}>}Iss_Age),Iss_Age))

edit:

No, this should be correct:

=AVG(Aggr(sum({<Iss_Status = {'Open', 'On-Hold'}>}Iss_Age),Issue_ID))

Highlighted Not applicable

Re: Average Age Calculation in Text Box

display the aggregate total of the number of days as well as the number of rows in the qlikView application - that will tell you how it is cvalculating Partner

Re: Average Age Calculation in Text Box

Just do: =Avg({<Iss_Status = {'Open', 'On-Hold'}>}Iss_Age) Not applicable

Re: Average Age Calculation in Text Box

Excell is aggregating a value of 7232 and dividing by 37 (the number of open and on-hold)

verifying in Qlik will tell you how Qlik is coming up with average Not applicable

Re: Average Age Calculation in Text Box

I also do not believe you need to do an Aggr if you are using the average function - you would only need the aggr function for example if you were going to calcualte the average yoursel you would aggregate and then divide the aggregate by the number of entries

The avg function does that for you MVP

Re: Average Age Calculation in Text Box

=AVG(Aggr(sum({<Iss_Status = {'Open', 'On-Hold'}>}Iss_Age),Issue_ID))  Not applicable

Re: Average Age Calculation in Text Box

Try one of the two options.