5 Replies Latest reply: Sep 6, 2016 11:49 AM by Sarah Nguyen

# Calculating averages for different categories

I'm new to QlikSense and need help with 3 calculations:

1. Need to calculate 6 mo average for clinical category
• Take current month (Jul), skip previous 3 months (Apr-Jun) and use prior 6 monts  (Oct-Mar) to calculate 6month average for CLINICAL
2. Need to calculate 6 mo average for surgery category
• Take current month (Jul), skip previous 3 months (Apr-Jun) and use prior 6 monts  (Oct-Mar) to calculate 6month average for SURGERY
3. Calculate difference between the 6 mo averages (in #1 and #2 above) and the [% of pmt to charges] based on the appropriate category (clinical vs surgery). If diff is less than zero, then it should display zero.

I have highlighted in yellow in image below what I'm trying to get to. Thank you.

• ###### Re: Calculating averages for different categories

Would you be able to provide a sample to use and create a sample application for you? Images are difficult to copy and paste

• ###### Re: Calculating averages for different categories

Sunny - here is the excel file. I am not sure how to create a sample qvf file with just this data.

• ###### Re: Calculating averages for different categories

Hi

On which field do you would like have AVG calcilation.

or

please try some functions in pivot table like

Rangavg(above(sum(.................)

• ###### Re: Calculating averages for different categories

The field I would like to have AVG calculation is the % of Pmt to Charges

• ###### Re: Calculating averages for different categories

Thanks Allu. Turns out your recommendation worked. Here's what I ended up using"

-(rangesum(above(sum({<Charges_Department={'Clinical'},[EncounterCalendar.MonthsAgo]={">=1<14"}>}Payment_Total),4,10))

/rangesum(above(sum({<Charges_Department={'Clinical'},[EncounterCalendar.MonthsAgo]={">=1<14"}>}amt),4,10)))