Calculating running average by excluding some values

[pgloc2020]

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

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.

[G3S]

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]

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]

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

[pgloc2020]

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

Result is not excluding Weekending 7/23/2022