Calculating averages for different categories

[#1]

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.

[sunny_talwar]

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

[Anonymous · Accepted solution]

Hi

On which field do you would like have AVG calcilation.

or

please try some functions in pivot table like

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

[#4]

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

[#5]

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

[#6]

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