
Re: How to find Avg and Max of a calculated field in QlikSense?
Sunny Talwar Apr 17, 2016 10:31 AM (in response to Nikita Naresh)May be try this:
Avg(Aggr(Sum(Rate_calculated)/Count(DISTINCT Date), name))

Re: How to find Avg and Max of a calculated field in QlikSense?
Sunny Talwar Apr 17, 2016 10:32 AM (in response to Sunny Talwar )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))

Re: How to find Avg and Max of a calculated field in QlikSense?
Nikita Naresh Apr 17, 2016 11:46 AM (in response to Sunny Talwar )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.

Re: How to find Avg and Max of a calculated field in QlikSense?
Sunny Talwar Apr 17, 2016 5:04 PM (in response to Nikita Naresh)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.

Re: How to find Avg and Max of a calculated field in QlikSense?
Srikanth P Apr 17, 2016 10:17 PM (in response to Nikita Naresh)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)

