# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted Partner

## How to find Avg and Max of a calculated field in QlikSense?

I have a table as follows

 Date Name Denominator Category Score Sep A 50332 None 3102 Sep A 50332 Min 2416 Nov A 47030 Medium 2338 Nov A 47030 Max 1710 Sep B 43792 None 629 Sep B 43792 Min 567 Sep B 43792 Max 552 Mar C 33036 None 434 Mar C 33036 Min 497 Mar C 33036 Medium 529 Mar C 33036 Max 890 Apr C 39322 None 841 Apr C 39322 Min 1493 Apr C 39322 Medium 1702 Apr C 39322 Max 1521

This is the result of a crosstable since my categories were previously different columns in my source file. That's why the denominator, date, and name are duplicated across rows.

I have calculated a field in my script  - ((Score)/(denominator)) *100as Rate_Calculated so that a rate is calculated in every row. On the front end, I do sum(Rate_calculated)/count(distinct Date) to get the averge rate for each name.

How can I find the average Rate calculated for all Names across dates and categories? Also, how can I find the largest and smallest rates?

I've tried Avg(Total Rate_calculated), sum({<Name=>}Rate_Calculated)/count(distinct Date) but they don't seem to work.

Need this urgently, so appreciate any help!

Tags (2)
5 Replies MVP

## Re: How to find Avg and Max of a calculated field in QlikSense?

May be try this:

Avg(Aggr(Sum(Rate_calculated)/Count(DISTINCT Date), name)) MVP

## Re: How to find Avg and Max of a calculated field in QlikSense?

For min and max, may be this:

Min(Aggr(Sum(Rate_calculated)/Count(DISTINCT Date), name))

Max(Aggr(Sum(Rate_calculated)/Count(DISTINCT Date), name)) Partner

## Re: How to find Avg and Max of a calculated field in QlikSense?

Thanks so much! Follow up question - How can I exclude some rows from being counted in this calculation?

I have a flag set up in my script called Exclude. Based on some conditions, it's 1 if the row should be excluded from this calculation or 0 if it should be included. I've done - Avg(Aggr(Sum({<Exclude={'0'}>}[Rate_Calculated])/Count(DISTINCT Date), name)) but I'm not getting the right answer.

Also, how can I compare the rate of 1 name against this overall average? I need to colour these rows based on whether they are below or above the overall average, but when I add the name dimension to the table, your formula doesn't stay static. MVP

## Re: How to find Avg and Max of a calculated field in QlikSense?

1st requirement;

Avg({<Exlcuded = {0}>}Aggr(Sum({<Exlcuded = {0}>}Rate_calculated)/Count({<Exlcuded = {0}>}DISTINCT Date), name))

For your 2nd requirement, I am not sure how you want to do the comparison. Can you share more information? May be give a sample with expected output. Would be very easy to help this way. Not applicable

## Re: How to find Avg and Max of a calculated field in QlikSense?

I guess, your 2nd requirement don't need AGGR function because you are adding chart object which using name as dimension. One more thing, you already calculated your rate in the script why you are doing both avg function and Sum/Count functions. Please find the below:

Dimension:

Name

Expression:

Avg for all :  avg(Rate_Calculated)

Min for all : min(Rate_Calculated)

Max for all : max(Rate_Calculated)

avg for None : avg({<Category={"None"}>}Rate_Calculated)

avg for Min : avg({<Category={"Min"}>}Rate_Calculated)

avg for Max : avg({<Category={"Max"}>}Rate_Calculated)

avg for Medium : avg({<Category={"Medium"}>}Rate_Calculated)