Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
pgloc2020
Creator
Creator

Calculating running average by excluding some values

Hello All,

I have 2 data sets, one contains only Open Cases and one contains all Closed cases. Week-Ending is the common key between these 2 data sets. Both datasets are joined in Qlik via Week_Ending 

I have a simple table chart where I added the following expression to get the total and average of Open and closed cases as shown in the screenshot-

Avg(Aggr(Count(CASE_ID), [export-1.WEEK_ENDING-WEEK_ENDING]))

and for Open - Closes cases - 

Avg(Aggr(Count([CASE_ID])-COUNT([export-1.CASE_ID]), [export-1.WEEK_ENDING-WEEK_ENDING]))

pgloc2020_0-1655996046823.png

Above expression gives me the total and overall average of all open and closed cases and the difference between them.

But my requirement is-

  • Calculate the average excluding the current week because it has partial data in it. For ex. I want to exclude week ending 6/25/2022 while calculating the average.
  • Calculate the average of  open and closed cases excluding the weeks where cases are 5 or less.

I am looking help to fulfill above 2 requirements. 

Thank you all in advance for your help.

Labels (2)
4 Replies
G3S
Creator III
Creator III

an if statement might help.. 

1. excuding current week:
if( weekend(today())<>[export-1.WEEK_ENDING-WEEK_ENDING],Avg(Aggr(Count(CASE_ID), [export-1.WEEK_ENDING-WEEK_ENDING])))

2. Excluding where avg is <5:
if((Avg(Aggr(Count([CASE_ID])-COUNT([export-1.CASE_ID]), [export-1.WEEK_ENDING-WEEK_ENDING])))>5,Avg(Aggr(Count([CASE_ID])-COUNT([export-1.CASE_ID]), [export-1.WEEK_ENDING-WEEK_ENDING])))

I am also learning, would be great to know if this works.

pgloc2020
Creator
Creator
Author

Hi, thank you for the reply. 

I tried using the above expressions but  on first one, since I don't have master calendar defined,  weekend(today) expression is not working. I generated the data including weekend via SQL query.

In the second expression, even though it does not show any error in the expression but no result appear. Its just "__"

 

G3S
Creator III
Creator III

weekend(today()) does not need master calendar.. I tried and got this for today. wkend.PNG

pgloc2020
Creator
Creator
Author

Hi,

Thank you for looking in to it. I tried using the same expression as you suggested in the column listed "Test"-

if( weekend(today())<>[export-1.WEEK_ENDING-WEEK_ENDING],Avg(Aggr(Count(CASE_ID), [export1.WEEK_ENDING-WEEK_ENDING])))

but I am getting same result as I am getting using following expression listed under column "All Open Claims"-

Avg(Aggr(Count(CASE_ID), [export-1.WEEK_ENDING-WEEK_ENDING]))

 

pgloc2020_0-1658490907979.png

Result is not excluding Weekending 7/23/2022