Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
nikita42
New Contributor II

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

I have a table as follows

     

DateNameDenominatorCategoryScore
SepA50332None3102
SepA50332Min2416
NovA47030Medium2338
NovA47030Max1710
SepB43792None629
SepB43792Min567
SepB43792Max552
MarC33036None434
MarC33036Min497
MarC33036Medium529
MarC33036Max890
AprC39322None841
AprC39322Min1493
AprC39322Medium1702
AprC39322Max1521

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!

5 Replies

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

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

nikita42
New Contributor II

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.

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)